CODEWITHSUNDEEP
entity-frameworklinqef-core-2.0
p.desterlich
p.desterlich

Reputation: 177

ef core select records from tableA with matching fields in tableB

I'm trying to translate a query from raw sql to linq statement in EF Core 2

Query is something like this

SELECT * FROM TableA
WHERE FieldA = ConditionA
AND FieldB IN (SELECT FieldC FROM TableB WHERE FieldD = ConditionB)

FieldA and FieldB are from TableA, FieldC and FieldD are from TableB

I need all fields from TableA and none from TableB returned, so it should be something like

return Context.TableA
    .Where(x => x.FieldA == ConditionA)
    .[ some code here ]
    .ToList()

my current solution is to get two distinct result sets and join them in code, something like this

var listA = Context.TableA
    .Where(x => x.FieldA == ConditionA).ToList();
var listB = Context.TableB
    .Where(x => x.FieldD == ConditionB).Select(x => x.FieldC).ToList();
listA.RemoveAll(x => !listB.Contains(x.FieldB);
return listA;

i hope it works, i still have to run tests on it, but i'm looking for a better solution (if anything exists)

Upvotes: 1

Views: 103

Answers (2)

code passionate
code passionate

Reputation: 108

It's a simple join that needs to be applied - 

var result = (from a in Context.TableA.Where(x => x.FieldA == ConditionA )
         join b in Context.TableB.Where(x => x.FieldD == ConditionB) on a.FieldB equals b.FieldC
         select new {a}
         ).ToList();

Upvotes: 0

GGO
GGO

Reputation: 2748

You can simply use Contains function in query like this :

var ids = Context.TableB
     .Where(p => p.FieldD == conditionD)
     .Select(p => p.FieldC);
var result = Context.TableA
     .Where(p => ids.Contains(p.FieldB))
     .ToList();

Upvotes: 1

Related Questions