Nate Greene Nate Greene - 21 days ago 8
C# Question

Can't get Dynamic Linq to work

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.

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. 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");