Join Linq to Entity's Vb.net on the left

entity-framework linq vb.net

Question

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.

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?

1
14
4/11/2011 10:20:19 PM

Accepted Answer

Similar question: Linq to Sql: Multiple left outer joins

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

LINQ Examples from: 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.

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
33
5/23/2017 12:33:33 PM

Popular Answer

For left join in VB.net we can use 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

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()


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow