左加入 Linq 到 Entity 的 Vb.net

Left Join Linq to Entity's Vb.net

提问by Ezi

I can't figure out that linq to entity query syntax. My problem is that if the value of the Calls table is null then noting comes up, I want to make something like a left join to get 'all' rows from the Calls table.

我无法弄清楚 linq to entity 查询语法。我的问题是，如果 Calls 表的值为空，则出现注释，我想进行左连接之类的操作以从 Calls 表中获取“所有”行。

I tried to group it but I can't figure out the correct way to write it.

我试图对它进行分组，但我想不出正确的写法。

Dim TicketQuery As ObjectQuery = From c In EnData.Customer _
                                         Join t In EnData.Calls On t.CustomerID Equals c.CustomerID _
                                         Join Status In EnData.Lists On t.Status Equals Status.ListValue _
                                         Join Project In EnData.Lists On t.Project Equals Project.ListValue _
                                         Join Priorty In EnData.Lists On t.Priority Equals Priorty.ListValue _
                                         Where c.Status > -1 And t.Status > -1 And Status.ListType = 1 And Project.ListType = 3 And Priorty.ListType = 2 _
         Select New With {c.CustName, t.CallID, t.CallDate, t.CallTime, t.Description, Key .Status = Status.ListText, Key .Project = Project.ListText, t.DateModified, Key .Priority = Priorty.ListText}

How can I fix that?

我该如何解决？

回答by Jeremy

Similar question: Linq to Sql: Multiple left outer joins

类似问题：Linq to Sql: Multiple left outer joins

Microsoft Documentation: http://msdn.microsoft.com/en-us/library/bb918093.aspx#Y916

微软文档：http: //msdn.microsoft.com/en-us/library/bb918093.aspx#Y916

LINQ Examples from: http://msdn.microsoft.com/en-us/vbasic/bb737909

LINQ 示例来自：http: //msdn.microsoft.com/en-us/vbasic/bb737909

Left Outer Join A so-called outer join can be expressed with a group join. A left outer joinis like a cross join, except that all the left hand side elements get included at least once, even if they don't match any right hand side elements. Note how Vegetables shows up in the output even though it has no matching products.

左外连接 所谓的外连接可以用组连接来表示。左外连接类似于交叉连接，除了所有左侧元素至少被包含一次，即使它们不匹配任何右侧元素。请注意即使没有匹配的产品，Vegetables 也是如何显示在输出中的。

Public Sub Linq105()
    Dim categories() = {"Beverages", "Condiments", "Vegetables", "Dairy Products", "Seafood"}

Dim productList = GetProductList()

Dim query = From c In categories _
            Group Join p In productList On c Equals p.Category Into Group _
            From p In Group.DefaultIfEmpty() _
            Select Category = c, ProductName = If(p Is Nothing, "(No products)", p.ProductName)

For Each v In query
    Console.WriteLine(v.ProductName + ": " + v.Category)
Next
End Sub

回答by Mohammad Karimi

For left join in VB.net we can use Let

对于 VB.net 中的左连接，我们可以使用 Let

Dim q = 
    (From item In _userProfileRepository.Table
     Let Country = (From p In _countryRepository.Table Where p.CountryId = item.CurrentLocationCountry Select p.Name).FirstOrDefault
     Let State = (From p In _stateRepository.Table Where p.CountryId = item.CurrentLocationCountry Select p.Name).FirstOrDefault
     Let City = (From p In _stateRepository.Table Where p.CountryId = item.CurrentLocationCountry Select p.Name).FirstOrDefault
    Where item.UserId = item.ProfileId.ToString)

After the left join we can use group by

在 left join 之后我们可以使用 group by

Dim q2 = 
    (From p In q Group p By p.Country, p.State, p.City Into Group 
     Select New With 
         {
             .Country = Country, 
             .State = State, 
             .City = City, 
             .Count = Group.Count
         }).ToList()