将“is null”转换为linq to sql语句

I am having trouble replicating the following sql as a LINQ statement

我无法将以下sql复制为LINQ语句

select TableA.* from TableA left outer join TableAinTableB on TableA.Id = TableAId where TableBId is null

The following returns no lines

以下不返回任何行

from TableA in db.TableA join AinB in db.TableAinTableB on TableA.Id equals TableAId where AinB.TableBId == null select TableA

Also tried and a few other things that didn't work.

还尝试了一些其他不起作用的东西。

from TableA in db.TableA join AinB in db.TableAinTableB on TableA.Id equals TableAId where AinB == null select TableA

TableAinTableB is a many to many table. The query I want will pull all the records from TableA that have no records in the middle table. My sql does what I want but I have no idea how to convert it to LINQ to SQL.

TableAinTableB是一个多对多的表。我想要的查询将从TableA中提取中间表中没有记录的所有记录。我的sql做我想要的但我不知道如何将它转换为LINQ to SQL。

I ended up working around it by just doing a db.ExecuteQuery("working sql"); But I would like to know if the query is possible in LINQ and how to write it, or a pointer to a document that covers this scenario. My searching did not uncover anything I found useful.

我最后只是做了一个db.ExecuteQuery(“working sql”);但我想知道LINQ中是否可以进行查询以及如何编写它,或者指向涵盖此方案的文档的指针。我的搜索没有发现我认为有用的任何东西。

3 个解决方案

#1

You can use the DefaultIfEmpty to simulate an outer join. Check out this sample.

您可以使用DefaultIfEmpty来模拟外部联接。看看这个样本。

In your example it's something like:

在您的示例中,它类似于:

var q = from a in TableA
            join b in TableB on a.Id equals b.Id into g
            from b in g.DefaultIfEmpty()
            select a;

#2

For "IS NULL" try something like this

对于“IS NULL”尝试这样的事情

from TableA in db.TableA 
join AinB in db.TableAinTableB 
on TableA.Id equals TableAId 
where object.Equals(AinB.TableBId, null)
select TableA;

#3

You can also use ExecuteQuery method of LINQ, for example:

您也可以使用LINQ的ExecuteQuery方法,例如:

string query = "select TableA.* from TableA left outer join TableAinTableB on TableA.Id = TableAId where TableBId is null";
TestDataContext db = new TestDataContext();
IEnumerable<TableA> objUser = db.ExecuteQuery<TableA>(query).ToList();

where TestDataContext is the LINQ-to-SQL DataContext for the database in which TableA is mapped.

其中TestDataContext是映射TableA的数据库的LINQ-to-SQL DataContext。

#1