-1

I have a SQL Serve table that contains two fields, date of type System.DateTime and id of type int

I also have a collection of objects in a list which contain the same structure or data and I am looking to extract the matches from the database

var itemsToDelete = db.RecordsTable.Where(
    dbRecord => myList.Any(
        item => item.PhysicalID == dbRecord.PhysicalId && item.date.Equals(dbRecord.date)
    )
);

However, when I run this it throws an exception of "Unable to create a constant value of type 'RecordsTable'. Only primitive types or enumeration types are supported in this context."

What am I missing?

Colonel Mustard
  • 1,482
  • 2
  • 17
  • 42
  • 1
    Possible duplicate of [LINQ to Entities - where..in clause with multiple columns](https://stackoverflow.com/questions/6912733/linq-to-entities-where-in-clause-with-multiple-columns) – Tim Rogers Jun 22 '17 at 10:47
  • 3
    `myList` is a local list and can't be used to generate a SQL query for multiple columns unless you use `db.RecordsTable.AsEnumerable().Where....` which pulls all the data to in-memory set. – Zein Makki Jun 22 '17 at 10:52

1 Answers1

0

You can extract both the projection fields in list and apply where.

var physicalIds = myList.Select(x => x.PhysicalID).Distinct().ToList();
var dates = myList.Select(x => x.date).Distinct().ToList();
var itemsToDelete = db.RecordsTable.Where(dbRecord => physicalIds.Contains(dbRecord.PhysicalID) && dates.Contains(dbRecord.date)).ToList();

After getting records in-memory, you can apply join to filter your exact match by both projection field with AND condition:

 itemsToDelete = (from itm in itemsToDelete
                join lst in myList on new {itm.PhysicalID, itm.date} equals new {lst.PhysicalID, lst.date}
                select new itm).ToList();
Tejas Vaishnav
  • 492
  • 2
  • 14
  • This is not a good way if the records are ten of thousands which I think what is the dilemma facing by OP. Having to say that, I am not able to provide alternatives :( – Sam Aug 29 '20 at 06:51