ArsalanK February 2016

Linq to Sql- Dynamic column select

Iam working on Linq-to-Sql trying to get column name which passes on dynamic obj parameter(changes every time). I need to select column name which define dynamic obj array.

P.S: I need to select sku,vendorname,vendorstylecode at once

"where" clause condition is working fine but I need to select specific column which define in data.col index

I tried below code but not helping me:

public HttpResponseMessage PostGenerateFile([FromBody] dynamic data) {
string[] vendorname = data.vendorname != null ?data.vendorname.ToObject<string[]>() : null;
string[] brandname = data.brandname != null ? data.brandname.ToObject<string[]>() : null;

using (var context = new Vendor_InvDataContext())
        { var query = context.AllInventories.AsQueryable();


            for (int i = 0; i < data.col.Count; i++)
            {
                if(data.col[i]=="SKU")
                   query.Select(s => s.SKU);
                if (data.col[i] == "VENDORNAME")
                    query.Select(s => s.VENDORNAME);
                if (data.col[i] == "VENDORSTYLECODE")
                    query.Select(s => s.VENDORSTYLECODE);
                if (data.col[i] == "STYLECODE")
                    query.Select(s => s.STYLECODE);
                if (data.col[i] == "STYLENAME")
                    query.Select(s => s.STYLENAME);
            }

 if (vendorname != null && vendorname.Length > 0)
            {
                query = query.Where(s => vendorname.Contains(s.VENDORNAME));
            }
            if (brandname != null && brandname.Length > 0)
            {
                query = query.Where(s => brandname.Contains(s.BRANDNAME));
            }
var items = query.ToList();

Answers


tede24 February 2016

You need to assign the result of query.Select(...) to query variable to actually change the query, which in your case you are not doing so your Select don't have any effect. But once you do it you will have a projection of result and the rest of the fields won't be available neither to add more fields to the projection nor to add where conditions.

So that approach will never work for the Select statement. You need to build a dynamic Expression Tree to select desired properties instead. Fortunately, somebody has already worked on this here, please look at @dotlatice answer which is what you need imo.

So, you code would be:

  1. Apply where condition as you are doing now
  2. Build an expression tree that produces an anonymous type containing your desired columns

Post Status

Asked in February 2016
Viewed 1,891 times
Voted 11
Answered 1 times

Search




Leave an answer