Convert SQL to Entity Framework Linq convert to list C#

Question

I am stuck at selecting a list of ID based on another list of ID in Entity Framework.

I googled and saw some examples but I am unable to find the answer I am looking for

SELECT ID 
FROM Main m 
INNER JOIN MainMapping cm ON mm.MainId = m.ID
WHERE mm.SecondId IN (1, 2, 3, 4, 5, 6)

I am trying to convert this SQL to EF Linq.

Here is what I got so far:

var listOfId = await _context.Main
                      .Include(main => main.MainMapping)
                      .Where(main => main.MainMapping 
                          .Where(mapping => request.SecondIdList.Any(y => y == mapping.SecondId)))
                      .ToListAsync();

But I keep getting errors. What am I missing?

These are the errors:

Error CS1662 Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type

Error CS0029 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<Testing1.Domain.Entities.MainMapping>' to 'bool'

Felipe Oriani · Accepted Answer · 2021-11-02 11:34:44Z

1

Ok, first, everytime you call .Where to filter something, you must return a boolean expression. Given you are using Linq to SQL your expression should return something that is possible to parse to sql language.

About your queston, you could try using .Any on your collection property to return a boolean and Contains to generate an IN statement. Then, to return a collection of Id, use Select to change the output. EF will parse it to valid sql expression.

var listOfId = await _context.Main
    .Include(main => main.MainMapping)
    .Where(main => main.MainMapping.Any(mapping => request.SecondIdList.Contains(mapping.SecondId)))    
    .Select(man => main.Id)
    .ToListAsync();

edited Nov 2, 2021 at 11:34

answered Nov 2, 2021 at 3:12

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zorev Gnoz Over a year ago

Oh i see i see Thanks so much for ur help!!

Collectives™ on Stack Overflow

Convert SQL to Entity Framework Linq convert to list C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related