1

In my API, I am trying to compare a list of objects passed in from the client with a list of records of the same type in the database, based on multiple properties.

I.e I have a Codes table in the database, with a Code column and a TypeId column. I receive a list of Code objects from the Client application, each with a Code property and a TypeId property.

Using LINQ/Entity Framework, I want a list of all the database Codes which are in the input/client Codes list. That is, where the Code and TypeId properties are the same. Like an INTERSECT or JOIN based on multiple properties.

In SQL, one would typically use the following:

SELECT * FROM @InputCodes ic INNER JOIN Codes c ON ic.Code = c.Code AND ic.TypeId = ic.TypeId

How do I do this using Entity Framework? I've tried the following approaches:

List<Code> codes = new List<Code>() { }; // Assume this is being passed in from Client
codes.Add(new Code() { Code = "1234", TypeId = 1 });
codes.Add(new Code() { Code = "1234", TypeId = 2 });
codes.Add(new Code() { Code = "ABCD", TypeId = 2 });
codes.Add(new Code() { Code = "ZZZZ", TypeId = 3 });

  1. var result = await _db.Codes.Where(a => codes.Any(b => a.Code == b.Code && a.TypeId == b.TypeId)).ToListAsync();

  var result = from dbCode in _db.Codes
          join requestCode in codes
          on new { a = dbCode.Code, b = dbCode.TypeId } equals new { a = requestCode.Code, 
                   b = requestCode.TypeId }
          select dbCode;

Both queries result in the following error:

System.InvalidOperationException: 'The LINQ expression 'b => EntityShaperExpression: 
    MxNode.Common.DB.Models.Codes
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Code == b.Code && (int?)EntityShaperExpression: 
    MxNode.Common.DB.Models.Codes
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.TypeId == b.TypeId' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

I need this query to be evaluated server side as there could be thousands of codes and it would not be optimal to load all of them into memory before comparing/filtering by code and type.

De Wet van As
  • 904
  • 8
  • 27
  • Possibly related: [Do Not Waste Performance By Not Using Temp Tables With Entity Framework Core](https://www.thinktecture.com/en/entity-framework-core/temp-tables-in-3-1/) and [Entity Framework Core – Contains Check Via Temp Tables](https://www.thinktecture.com/en/entity-framework-core/temp-tables-in-2-1/) – Fildor Jan 13 '23 at 13:30
  • Use as shown here [FilterByItems](https://stackoverflow.com/a/71667616/10646316) function. With EF Core you cannot join to local collections. – Svyatoslav Danyliv Jan 13 '23 at 13:37
  • something about nullable in the model? – T.S. Jan 13 '23 at 14:32
  • No "out of the box" solution so far. You can try [MemoryJoin](https://github.com/neisbut/EntityFramework.MemoryJoin) or other 3rd party extensions mentioned in the other comments. In general the best solution for SqlServer would be TVP parameter, but it requires a concrete type definition to be created in the db, and also is not generalized for any type of database, even though every major db have some way to pass in memory "table" to SQL. – Ivan Stoev Jan 14 '23 at 03:48

0 Answers0