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

entity-framework linq vb.net

Question

That linq to entity query syntax eludes me. My issue is that nothing happens if the value of the Calls table is null, hence I need to create something like to a left join to get "all" data from the Calls table.

I tried grouping it, but I'm not sure how to do it properly.

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 correct that?

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

Accepted Answer

The same thing: Multiple left outer joins in Linq to Sql

Zzz-12-Zzz in Microsoft Documentation

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

Outer Left Join A group join may be used to indicate a so-called outer join. Similar to a cross join, a left outer join includes all left-side components at least once, even if they don't match any right-side elements. Observe how Vegetables appears in the output despite the fact that there are no goods that match it.

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

Let may be used in VB.net for left join.

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, group by may be used.

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