Left join processed in memory rather than translated to SQL

[Miika]

I have the following query implementing a "unified search" method

var searchObjects =
    from objectA in this.context.DB.objectAs
    join objectB in this.context.DB.objectBs on objectA equals objectB.objectA into objectAB
    from AB in objectAB.Where(o => o.Type == "BasicGroup").DefaultIfEmpty()
    select new { objectA, objectB = AB};

foreach (var searchWord in searchWords)
{
    var searchObjects =
        searchObjects.Where(p => p.objectA.Name.Contains(searchWord) ||
            (p.objectB != null &&
                (p.objectB.Name.Contains(searchWord) ||
                p.objectB.ID.contains(searchWord))));
}

In an earlier version I had used a subquery style of search, which worked, but was too inefficient. However, the above code fails with a NullReferenceException because of a null in the objectB.Name field. This caught me by surprise, because the earlier search hadn't cared, but as it turns out, this join style of query was in fact pulling everything into memory and processing it there rather than translating the query into SQL like it should be (I used the db.Log = Console.Out method to confirm this). If pulled into memory, then objectB.Name is a string -object-, and a contains can't be called on it if null.

Any ideas on why OpenAccess is not able to translate and process the above query as SQL?


EDIT: using version 2013.1.219.3 of OpenAccess

[Alexander]

Hello Miika,

Thank you for reporting this issue. We managed to reproduce it and it seems like a problem on our side. It is not caused by the dynamically appended where statements but is related to the construction of the join itself.
Unfortunately at this stage I am not able to provide you with a workaround. We will have to investigate the problem in more details and will let you know as soon as we have more information. Please excuse us for the inconvenience caused.

Kind regards,
Alexander
the Telerik team

[Miika]

Thanks for the answer. I was actually able to find a way to make it produce sensible SQL, but the system seems... fragile. One misplaced condition, and suddenly everything is drawn into memory again.

I updated to the latest version of OpenAccess (Q3 SP1 I think?), and this allowed me to put the "BasicGroup" condition as part of the join in the form of new { objectA.ID, groupType = "BasicGroup" } equals new { objectB.AID, objectB.groupType }, which is neat. However, it didn't solve the SQL vs. memory issue. Then I discovered that if I had a "where" clause as part of the query, this would force it draw it into memory, but where clauses added as lambda statements didn't.... Weird, eh?

So, by removing all where clauses from the linq query that sets up the data, and then just adding all filter criteria as .Where() lambda statements, it produces nifty sql, processed on the server and everyone is happy. Hopefully this additional information will help you narrow down the problem.

[Alexander]

Hi Miika,

I am glad to see that you have found a workaround.
Thank you for the additional information, it will greatly help us reproduce and locate the problem. We will let you know when this problem is fixed.

Kind regards,
Alexander
the Telerik team

[Nils C.]

I have the same problem, 

my code is always evaluated on the  client:
There a thousands on querys to the company table... the other ones are reflected with one query. This is base functionality not working..










 'Dim leads_filter4 = (From it In _db.LeadsSubsystemAware Join vt In _db.Gehoert_zu_Vertriebsaktion.Where(Function(x) vt_action_search_items.Contains(x.Vertriebsaktion_id)) On it.Id Equals vt.Lead_id _
        '    Group Join verant9 In _db.Benutzer On verant9.Id Equals it.Verantwortlicher_id Into of9 = Group From verant In of9.DefaultIfEmpty
        '    Group Join kunde9 In _db.Companies On it.Kunden_id Equals kunde9.Id Into kundegr = Group From kunde2 In kundegr.DefaultIfEmpty _
        '    Group Join projekt1 In _db.Projekt On projekt1.Angelegt_aus_lead_ID Equals it.Id Into _
        '    pr19 = Group From pr20 In pr19.DefaultIfEmpty Where (Not it.Verantwortlicher_id Is Nothing AndAlso verantwortliche_array.Contains(it.Verantwortlicher_id) _
        '    Or (show_empty_value_verantwortlicher = True And it.Verantwortlicher_id.HasValue = False)) And (it.Lead_status Is Nothing OrElse status_array.Contains(it.Lead_status)) Select _
        '   New Leads With {.Id = it.Id, _
        '         .Kunde = If(Not kundegr Is Nothing, New Company With {.Telefonnummer = kunde2.Telefonnummer, .Branche = kunde2.Branche, .Ort = kunde2.Ort, .PLZ = kunde2.PLZ, _
        '         .Land = kunde2.Land, _
        '         .Strasse = kunde2.Strasse, _
        '         .Name = kunde2.Name
        '             }, Nothing), _
        '                   .Kunden_id = it.Kunden_id, _
        '                   .Lead_Stati = it.Lead_Stati, _
        '                   .Lead_nr = it.Lead_nr, _
        '               .Lead_chance = it.Lead_chance, _
        '                   .Lead_cashdate = it.Lead_cashdate, _
        '                   .Projekt = If(Not pr20 Is Nothing, _
        '                 New Projekt With {.Projektnummer = pr20.Projektnummer _
        '            , .Projekt_Name = pr20.Projekt_Name}, Nothing),
        '                   .Lead_budget = it.Lead_budget,
        '                   .Verantwortlicher = If(Not verant Is Nothing, New Benutzer With {.Vorname = verant.Vorname, .Nachname = verant.Nachname}, Nothing)}).ToList

[Thomas]

Hi Nils,

I've tried to reproduce this effect here with a similar query:

var q =
from it in Scope.Extent<Northwind.Order>()
join vt in Scope.Extent<Northwind.Employee>()
                        .Where(x => names.Contains(x.LastName)) on it.EmployeeID equals vt.Id
join verant9 in Scope.Extent<Northwind.Customer>() on it.CustomerID equals verant9.Id into of9
from verant in of9.DefaultIfEmpty()
join kunde9 in Scope.Extent<Northwind.Supplier>() on it.Id equals kunde9.Id into kundegr
from kunde2 in kundegr.DefaultIfEmpty()
join projekt1 in Scope.Extent<Northwind.Region>() on it.Id equals projekt1.Id into pr19
from pr20 in pr19.DefaultIfEmpty()
where (!(it.ShipCity == null)) && it.RequiredDate.HasValue
select new {
  A = it.OrderDate,
  B = !(kundegr == null) ? new { C = kundegr } : null,
  D = pr20 != null ? new { E = pr20.RegionDescription } : null
};

I know that this is not having the absolute same structure than in your case, but I do not get a bad behavior there. The query produces exactly one SQL statement.
This could be caused by fixes we did since the 2013.1.418 version (or are you using a newer one?). Or it could be caused by your data model (which I dont have).
Also, you should try to put the filter conditions more closely to the sources if possible:
Where (Not it.Verantwortlicher_id Is Nothing AndAlso verantwortliche_array.Contains(it.Verantwortlicher_id) _
        '    Or (show_empty_value_verantwortlicher = True And it.Verantwortlicher_id.HasValue = False)) And (it.Lead_status Is Nothing OrElse status_array.Contains(it.Lead_status))
This could be applied directly to the 'it' source, before anything is joined. This could reduce the number of added queries greatly.

Our next version is scheduled for end of February. In case your code is still showing a suboptimal execution with the current version we would like to get your model so that we can see why this is the case.

Regards,
Thomas
Telerik