Nate Greene - 6 months ago 30

C# 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.

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