Can't get Dynamic Linq to work

c# dynamic-linq entity-framework linq

Question

I am trying to work with the Dynamic Linq library for querying my entity framework data source. I have added the correct package to my project and have used the following syntax

 var salesEntities = dashboardEntity.FactSales.Where(d => d.DateKey >= startDate).Where(d => d.DateKey <= endDate)
                                    .Where(c => c.CompanyID == companyID)
                                    .Where("StoreID==@0",1)
                                    .ToList();

I have also tried

 .Where("StoreID=1")

based on the example I found on the ScottGu blog post that most dynamic query SO questions seem to suggest. Can anyone help me see what I am missing to make this work?

It doesnt produce and error it just fails to return any data at all.

1
2
11/15/2016 8:47:24 AM

Accepted Answer

TL;DR

Are you sure you need Dynamic LINQ for this?

List<int> storeIDs = new List<int>() {1,2,3};
var salesEntities = dashboardEntity.FactSales
    .Where(d => d.DateKey >= startDate)
    .Where(d => d.DateKey <= endDate)
    .Where(c => c.CompanyID == companyID)
    .Where(c => storeIDs.Contains(c.StoreID))
    .ToList();

Long version

LINQ allows for compiler-generated expressions:

IQueryable<FactSales> qry;
qry = qry.Where(x => x.DateKey >= startDate);

If the expressions need to change at runtime, it is possible to build the expression using static methods in the System.Linq.Expressions.Expression class:

//using static System.Linq.Expressions.Expression;

//x
var parameter = Parameter(typeof(FactSale));

//x.DateKey
var dateKey = MakeMemberAccess(parameter, typeof(FactSales).GetProperty("DateKey"));

//(the value in startDate, as if it had been written in)
var startDateConst = Constant(startDate);

//x.DateKey >= (value of startDate)
var comparison = GreaterThanOrEqual(dateKey, startDateConst);

//x => x.DateKey >= (value of startDate)
var lmbd = Lambda<Func<FactSale,bool>>(comparison, new [] {prm});

//pass the expression into the Queryable.Where method
qry = qry.Where(lmbd);

The Dynamic LINQ library (whose latest incarnation can be found here) allows generating expressions using strings:

IQueryable<FactSales> qry;
qry = qry.Where("DateKey >= @0", startDate);

Dynamic LINQ is extremely useful when the expression is not known at compile time, but in this case, the expression is known at compile time (in this case, using the List<T>.Contains method. Therefore I don't see any reason to use Dynamic LINQ here.

N.B. I still don't know why this doesn't work:

var qry = dashboardEntity.FactSales.Where("StoreID = 1");
2
11/16/2016 6:55:38 PM

Popular Answer

Idk if you sorted this out but I just noticed your reply to my comment.

I think what you are missing is = instead of == while comparing StoreID.

var salesEntities = dashboardEntity.FactSales.Where(d => d.DateKey >= startDate).Where(d => d.DateKey <= endDate)
                                .Where(c => c.CompanyID == companyID)
                                .Where("StoreID = @0",1)
                                .ToList();


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow