Failed to create constant value of type anonymous type only primitive types

Question

Using Entity Framework Version = 6.0.0.0 to get a common identifier and order, as shown below.

var dt1 = from p in dt.AsEnumerable()
          select new
          {
              Id = p.Field<int>("Id"),
              OrderId = p.Field<int>("OrderId")
          };

var dt2 = (from order in db.Orders
           select new
           {
               order.Id,
               order.OrderId
           }).ToList();
var intersect = dt1.Intersect(dt2);

Based on a list of values ​​at the intersection. I need to select all the values ​​from the order table.

Trying to use the error code "failed to create a constant value of type anonymous type only primitive types"

var result= (from a in sync.Orders
              where intersect.Any(b => a.Id == b.Id && a.OrderId == b.OrderId)
              select a).ToList();

Answer 1

You are trying to "join" an EF query with a dataset in memory that does not work because there is no way to embed a list and search in SQL. One option is to dump the entire table into memory with AsEnumerable:

var result= (from a in sync.Orders.AsEnumberable
              where intersect.Any(b => a.Id == b.Id && a.OrderId == b.OrderId)
              select a).ToList();

- Id OrderId Contains, IN SQL:

var lookup = intersect.Select(i => i.Id + "-" + i.OrderId).ToList();

var result= (from a in sync.Orders
              where lookup.Contains(a.Id + "-" + a.OrderId)
              select a).ToList();