How to write this T-SQL in LINQ to sQL?
select * from ta开发者_如何学编程ble1 where pkey1 in
(select pkey2 from table2 where column1='abc')
Where pkey1 and pkey2 are both int columns.
something like:
from t1 in table1
let x = from t2 in table2 where t2.column1.Equals("abc") select t2
where t1.pkey1.Equals(x.pkey2)
select t1;
and you ending up know what let
helps you to do :)
Not a LINQ answer, but a perfectly valid LINQ-to-SQL one:
var results = ctx.ExecuteQuery<Table1>(@"
select * from table1 where pkey1 in
(select pkey2 from table2 where column1='abc')").ToList();
You don't have to hand over control of every query to LINQ; indeed, in many cases a well-written custom TSQL query can be far more efficient that one generated from LINQ-to-SQL. Which isn't a criticism of LINQ-to-SQL (it does a great job for the majority of queries that are simple etc).
from t in table1
join u in table2 on t.pkey1 equals u.pkey2
where u.column1 == "abc"
select t;
var query = from a in db.table1
join b in db.table2 on a.pkey1 equals b.pkey2
where b.column1 == "abc"
select a;
Here is another possible query:
var q = from t2 in table2.Where(x => x.column1 == "abc")
from t in table1.Where(x => x.pkey1 == t2.pkey2)
select t;
You might need to call Distinct()
on the results depending on how you want to use it. The generated SQL is equivalent to:
SELECT [t1].*
FROM table2 AS [t2], table1 AS [t1]
WHERE ([t2].[column1] = 'abc') AND ([t1].[pkey1] = [t2].[pkey2])
精彩评论