Convert t-sql query to linq

Question

I have this t-sql query and I need to convert it to linq

SELECT m.field1
            FROM table1 m
            Join table2 v on v.field1 = m.field1 and v.field2 = m.field3
            WHERE v.field4 =4
            AND m.field1=125325
            AND m.field5=0
            AND m.field1 IN (SELECT field1 FROM table3)

any help ?

Possible duplicate: http://stackoverflow.com/questions/3217669/how-to-do-a-join-in-linq-to-sql-with-method-syntax — David, Jun 11 '13 at 09:13
What flavour of LINQ do you need? Are we talking .net? And if so vb or c#? — twoleggedhorse, Jun 11 '13 at 09:26
Show a data model. Tell which linq flavour you use. Show what you tried. Tell why you didn't succeed. StackOverflow is not a take-out window. — Gert Arnold, Jun 11 '13 at 09:38

score 1 · Accepted Answer · answered Jun 11 '13 at 09:41

As long as all tables related, and you don't need to do relations it LINQ, so it will be something like:

     var result = db.table1.WHERE(p=>p.table2.field4 == 4 && p.field1 == 125325 
     && p.field5 == 0 && p.table3 != null)

Where db - is your DbContext or ObjectContext

lisp · Answer 2 · 2013-06-11T13:52:00.337

Query:

from m in db.table1
join v in db.table2
on new { m.field1, Field2 = m.field2 } 
equals new { v.field1, Field2 = m.field3 }
where ...
where db.table3.Select(x => x.field1).Contains(m.field1)
select m.field1;

Method chain:

db.table1
    .Join(
        db.table2, 
        m => new {m.field1, Field2 = m.field2}, 
        v => new {v.field1, Field2 = m.field3},
        (m, v) => new {m, v})
    .Where(...)
    .Where(t => db.table3.Select(x => x.field1).Contains(t.m.field1))
    .Select(t => t.m.field1);

Convert t-sql query to linq

2 Answers2