how to create this query with lambda expression or query expression?

Question

I have this tables:

TableA(IDTableA, ...)
TableAC(IDTableA, IDTableC)
TableC(IDtableC...)

I have the entities TableA y TableC but not for the TableB. In the entity TableA I have a collection ICOllection and in the tableC I have a collection Icollection.

I would like to get all the registers in TableA that has registers in TableC which TableC has a ID = 5.

In T-SQL whould be:

select * from TableA, TableAC, TableC
where TableA.IDTableA = TableAC.IDTableA
and TableC.IDTableC = TableAC.IDTableB
and TableA.IDTable = 5;

How can I do this query with lambda expressions? Syntax query is another option too.

Thanks.

possible duplicate of Entity Framework Many to Many query

Gert Arnold
– Gert Arnold

2014-05-21 21:36:56 +00:00
Commented May 21, 2014 at 21:36 — Gert Arnold
– Gert Arnold, Commented May 21, 2014 at 21:36

sondergard · Accepted Answer · 2014-05-21 20:55:31Z

I think the entity framework omits the relation table AC when generating the entites, and instead generates and populates the ICollection properties. In essence it makes the many-2-many relation invisible. To get all entities from TableA which has a relation to TableC = 5, you can directly make a LINQ query to this ICollection:

from ta in TableA
where ta.TableC.Any(tc => tc.Id == 5)
select ta;

UPDATE: As Tough Coder mentioned - LINQPad truly is your friend when it comes to testing linq queries. Not to mention that is invaluable as a code scratchpad (just testing small samples without firing up a completely new VS project). I just made a complete sample to illustrate the entity relation and linq query you need:

void Main()
{
    List<TableA> ta = new List<TableA>();

    var ta1 = new TableA() { Id = 1 };
    var ta2 = new TableA() { Id = 2 };
    var ta3 = new TableA() { Id = 3 };

    ta.Add(ta1);
    ta.Add(ta2);
    ta.Add(ta3);

    var tc1 = new TableC() { Id = 1 };
    var tc2 = new TableC() { Id = 2 };
    var tc3 = new TableC() { Id = 3 };

    ta1.TableCs.Add(tc1);
    ta1.TableCs.Add(tc3);

    ta2.TableCs.Add(tc1);

    ta3.TableCs.Add(tc2);
    ta3.TableCs.Add(tc3);

    var res = from t in ta
              where t.TableCs.Any(tc => tc.Id == 3)
              select t.Id;

    foreach (var t in res)
    {
        Console.WriteLine(t);
    }
}

public class TableA
{
    public int Id {get;set;}
    public List<TableC> TableCs{get;set;}
    public TableA()
    {
        TableCs = new List<TableC>();
    }
}

public class TableC
{
    public int Id {get;set;}
    public List<TableA> TableAs{get;set;}
    public TableC()
    {
        TableAs = new List<TableA>();
    }
}

Bura Chuhadar · Accepted Answer · 2014-05-21 21:01:17Z

1

In a long way you can do this:

from c in db.TableA
where c.TableC.Any(tc => tc.Id == 5)
select c;

If you want to check this code in SQL, You can use LINQPad. It will show this Linq query in SQL.

For another solution you might just send your SQL query by using ExecuteStoreQuery. Such as:

db.ExecuteStoreQuery<Results_Class>("select * from TableA, TableAC, TableC
where TableA.IDTableA = TableAC.IDTableA
and TableC.IDTableC = TableAC.IDTableB
and TableA.IDTable = 5;");

For this solution you have to create a Results_Class class.

edited May 21, 2014 at 21:01

answered May 21, 2014 at 20:31

Bura Chuhadar

3,7711 gold badge16 silver badges17 bronze badges

3 Comments

Álvaro García Over a year ago

My problem it's that I don't have the table TableAC in EF becase EF does not create the entity for this kind of tables.

sondergard Over a year ago

@ÁlvaroGarcía Entity framework omits the M2M table and populates the ICollection properties instead. You can make the linq query into this instead - check my answer.

Bura Chuhadar Over a year ago

@ÁlvaroGarcía I read your question too quickly. You can check my answer again.

Collectives™ on Stack Overflow

how to create this query with lambda expression or query expression?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related