CODEWITHSUNDEEP
c#linqado.netado.net-entity-data-model
blitzkriegz
blitzkriegz

Reputation: 9576

LINQ equivalent for SQL

I am trying to convert a ASP.NET project to Entity framework. How to re-write the following query to its LINQ equivalent?

SELECT {Table1 objects}
FROM [Table1] tb1
INNER JOIN [Table2] tb2
ON tb1.Table1ID = tb2.fk_Table1ID
WHERE tb2.fk_attrib1 = '123' AND tb2.fk_attrb2 = '345'
ORDER BY tb1.attrib1

The result is a collection of Table1 objects. Here Table1 and Table2 correspond to object System.Data.Objects.ObjectSet of ADO.NET Entity Framework.

Upvotes: 0

Views: 150

Answers (4)

Jarek
Jarek

Reputation: 3379

This should help you a little bit. I suppose the main problem is with JOIN clause - in EF you can use NavigationProperties and don't need to worry about joining tables - EF will take care of that for you.

Also you are trying to filter on column from joined table. This you can do using Any method to find all Table1 elements that are connected to Table2 where those referenced elements have certain properties/columns. You should also get familiar with All method, as it might be useful to you in future.

from t1 in context.Table1
where t1.Table2s.Any(t2.fk_attrib1 == "123" && t2 => t2.fk_attrb2 == "345")
order by t1.attrib1
select t1;

Edit:
I assume that there is 1:n relationship between Table1 and Table2 which results in enumerable collection as NavigationProperty in Table1 objects.

Edit2:
Fixed error in code - didn't noticed that both attributes are from Table2 not Table1

Upvotes: 1

Nick Butler
Nick Butler

Reputation: 24383

Something like this:

context.Table1
    .Where( o => o.Table2s.Any( o2 =>
        o2.fk_attrib1 == '123' &&
        o2.fk_attrib2 == '345' ) )
    .OrderBy( o => o.attrib1 )
    .ToList();

BTW, LINQPad is great for trying out L2E queries.

Upvotes: 2

Tigran
Tigran

Reputation: 62248

Should be something like this: 

var result = (from tb1  in Table1
                from tb2 in Table2
                      where tb1.Key == tb2.Key && 
                                 tb2.fk_attrib1 = '123' && 
                                   tb2.fk_attrb2 = '345'
            select ione).OrderBy(p=>p.attrib1);

Hope this helps.

Upvotes: 0

ken2k
ken2k

Reputation: 48975

var results = from tb1 in Context.Table1
                join tb2 in Context.Table2 on tb1.Table1ID == tb2.fk_Table1ID
                where tb2.fk_attrib1 == "123" && tb2.fk_attrb2 == "345"
                orderby tb1.attrib1
                select tb1;

Upvotes: 2

Related Questions