Using dictionary in LINQ Where clause

Question

I have a list of data called DataList. DataList is having field id and UpdatedTime and some other fields. On the other, I have a dictionary with just the id and its respective value UpdatedTime. Is there any way to filter DataList such that it satisfies the dictionary itemIdAndUpdatedTimeFilter item Id and their respective UpdatedTime is higher than that of the DataList's UpdatedTime?

Dictionary<int, DateTime> itemIdAndUpdatedTimeFilter = getFilter();
itemIdAndUpdatedTimeFilter.Add(1, dateA);
itemIdAndUpdatedTimeFilter.Add(2, dateB);
itemIdAndUpdatedTimeFilter.Add(3, dateC);

var dataList = getData();

var filtered = dataList.Where(d=>itemIdAndUpdatedTimeFilter.ContainsKey(d.Id) && itemIdAndUpdatedTimeFilter[d.Id] < d.UpdatedTime)

The above LINQ statement ended up in following exception:

LINQ to Entities does not recognize the method 'Boolean ContainsKey(Int32)' method, and this method cannot be translated into a store expression.

Tried deleted the ContainKey condition and get the following instead:

LINQ to Entities does not recognize the method 'System.DateTime get_Item(Int32)' method, and this method cannot be translated into a store expression.

Desired query is such that:

SELECT FROM dataList
WHERE (dataList.UpdatedTime < dateA AND dataList.Id = 1)
OR (dataList.UpdatedTime < dateB AND dataList.Id = 2)
OR (dataList.UpdatedTime < dateC AND dataList.Id = 3)

You can't use a dictionary to filter a database table, that is not translatable into a sql store expression. Or is the example sql only the comparable syntax for an in memory filter? — Igor, Sep 15 '17 at 15:03

score 2 · Accepted Answer · answered Sep 15 '17 at 15:31

Fetch all items from the database and filter the in-memory results:

var dataList = getData().ToList();
var filtered = dataList.Where(d=>itemIdAndUpdatedTimeFilter.ContainsKey(d.I‌d) && itemIdAndUpdatedTimeFilter[d.Id] > d.UpdateTime)):

The other option would be to execute raw SQL or a stored procedure that performs the filtering in the database: https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx

Entity Framework won't be able to translate your filtering logic into a valid SQL query.

Using dictionary in LINQ Where clause

1 Answers1