ORM problem with Linq to SQL

Thread is closed for posting
6 posts, 0 answers
  1. Ben
    Ben avatar
    8 posts
    Member since:
    Mar 2011

    Posted 05 Oct 2015 Link to this post

    We have a couple of tables:
         GEBOUW (=Building)
          LAND (=Land)

    The last two are both coupled to VASTGOEDLEVEL0 ( = A Gebouw is a VASTGOEDLEVEL0, the other way around does not have to be the case)

    VASTGOEDOBJECT , a table that can have zero or more VASTGOEDLEVEL0 as children (via a VASTGOEDOBJECT property on the VASTGOEDLEVEL0)

    So querying the domain , for a GEBOUW gives everything of VASTGOEDLEVEL0 and GEBOUW as properties.

    We also have a ROLES table, that links to VASTGOEDLEVEL0 or a VASTGOEDOBJECT and provides different users with different ROLES for those entities.
    We also have CONTRACTS which could link a user to a CONTRACT on that VASTGOEDLEVEL0

    We have the following function to query the domain (the real one has some more parameters...):
    public IQueryable<T> GetFilteredVastgoedLevel0<T>(Gebruiker g, String sortBy, SortDirection sortDirection) where T : VastgoedLevel0 {    
    var predicate = PredicateBuilder.Create<T>();        

            //add the filtering on the party and the contracten and the rollen to the query            
                var predicateGebouwen = PredicateBuilder.Create<T>();            
                predicateGebouwen = predicateGebouwen.Or(x => x.Rollen.Any(z => z.Gebruiker == g));            
                predicateGebouwen = predicateGebouwen.Or(x => x.Klant != null && x.Klant == g.Party);            
                predicateGebouwen = predicateGebouwen.Or(x => x.Contracten.Any
                       (y => y.Inspectiebureau == g.Party && y.Status == ContractStatus.Actief && y.Ingangsdatum <= DateTime.Now && (!
                        y.Einddatum.HasValue || y.Einddatum.Value > DateTime.Now)));             predicateGebouwen = predicateGebouwen.Or(x => x.VastgoedObject !
                         = null && !string.IsNullOrEmpty(x.VastgoedObject.IsActief) && x.VastgoedObject.IsActief.Equals
                        ("Y", StringComparison.CurrentCultureIgnoreCase) && x.VastgoedObject.Rollen.Any(z => z.Gebruiker != null && z.Gebruiker == g));             predicate = predicate.And(predicateGebouwen);        
            if (string.IsNullOrEmpty(sortBy))             return Facade.GetQueryable<T>().ConditionalWhere(predicate);         else         {             return Facade.GetQueryable<T>().ConditionalWhere(predicate).SortBy(sortBy, sortDirection);         }     } }

    When this function is called with T = VASTGOEDLEVEL0 everything is fine,    the three conditions are used and three 'not required conditions)
    but when the function is called T = GEBOUW, the Roles is becoming on the VASTGOEDOBJECT becomes a problem. The SQL generated created a VASTGOEDLEVEL0 JOIN VASTGOEDOBJECT
    to query for the Roles.But this is wrong, Not every VASTGOEDLEVEL0 has a VASTGOEDOBJECT as parent. But because of the JOIN every VASTGOEDLEVEL0 with a VASTGOEDOBJECT as parent is excluded from the result. ANd as you can see the condition is clearly an OR part of the query.  Below we have the SQL generated when querying for GEBOUW  (first part) and VASTGOEDLEVEL0 second part, in the first pasr you see the JOIN with VASTGOEDLEVEL0 and VASTGOEDOBJECT,
    if this was a LEFT JOIN it would have been OK. The second query is generated by the same code but then T = GEBOUW, now there is no JOIN with Vastgoed_object at all!!!
    ​-------------------------- GEBOUW --------------------
    declare @p0 int
    declare @p1 int
    declare @p2 datetime
    declare @p3 datetime
    declare @p4 int
    set @p0=1
    set @p1=1
    set @p2='2015-10-01 09:55:33.083'
    set @p3='2015-10-01 09:55:33.083'
    set @p4=1
    SELECT a.[id] AS COL1, b.[voa_class] AS COL2, a.[Buffercapaciteit] AS COL3, a.[ivginspecteur_gebruikerId] AS COL4, a.[object_code] AS COL5,
    a.[TechnischeCapaciteit] AS COL6, a.[vertrouwelijk] AS COL7, b.[itk_gewenst] AS COL8, b.[itk_huidig] AS COL9, b.[aanmaakdatum] AS COL10, b.[actief_einddatum]
    AS COL11, b.[actief_startdatum] AS COL12, b.[addresshash] AS COL13, b.[alias] AS COL14, b.[partyId_assetManager] AS COL15, b.[asset_manager_enf] AS COL16,
    b.[partyId_assetOwner] AS COL17, b.[partyId_assetUser] AS COL18, b.[bebouwd] AS COL19, b.[beheerder] AS COL20, b.[beheerder_email] AS COL21, b.[beheerder_telefoon]
    AS COL22, b.[bouwaard] AS COL23, b.[bouwjaar] AS COL24, b.[bron] AS COL25, b.[bvo] AS COL26, b.[clustr] AS COL27, b.[complex] AS COL28, b.[eigendom] AS COL29,
    b.[eigendom_type] AS COL30, b.[ref_oracle_id] AS COL31, b.[gebouwfunctie_id] AS COL32, b.[gebouw_functie_enf] AS COL33, b.[grensbedrag_por] AS COL34, b.[huis_nummer]
    AS COL35, b.[inspectiebureau_id] AS COL36, b.[is_actief] AS COL37, b.[is_mef] AS COL38, b.[itk] AS COL39, b.[itkbrand] AS COL40, b.[itkenergetica] AS COL41, b.[itkinzicht]
    AS COL42, b.[itkonderhoud] AS COL43, b.[klant_id] AS COL44, b.[laatste_rgdboeiproces] AS COL45, b.[laatste_rgdboei_status] AS COL46, b.[laatste_wijziging_door] AS COL47,
    b.[land] AS COL48, b.[latitude] AS COL49, b.[longitude] AS COL50, b.[monument] AS COL51, b.[naam] AS COL52, b.[businesskey] AS COL53, b.[object_manager_enf] AS COL54,
    b.[object_visie_enf] AS COL55, b.[objectvisie] AS COL56, b.[onderdeel_complex] AS COL57, b.[onderhoudsniveau] AS COL58, b.[plaats] AS COL59, b.[planjaar] AS COL60,
    b.[postcode] AS COL61, b.[serviceovereenkomst] AS COL62, b.[status] AS COL63, b.[stoplichtblauw] AS COL64, b.[stoplichtdatum] AS COL65, b.[stoplichtgroen] AS COL66,
    b.[stoplichtoranje] AS COL67, b.[stoplichtrood] AS COL68, b.[straat] AS COL69, b.[uitvoeringsniveau] AS COL70, b.[vastgoed_object_id] AS COL71, b.[verwacht_rgdboeiproces]
    AS COL72, b.[verwijder_reden] AS COL73, b.[verwijderdatum] AS COL74, b.[wijzigingsdatum] AS COL75 FROM [COB].[gebouw] a JOIN [COB].[vastgoedlevel0] AS b ON (a.[id] = b.[id])
    JOIN [COB].[Vastgoed_Object] AS c ON (b.[vastgoed_object_id] = c.[id])
    JOIN [COB].[Vastgoed_Object] AS d ON (b.[vastgoed_object_id] = d.[id])
    JOIN [COB].[Vastgoed_Object] AS e ON (b.[vastgoed_object_id] = e.[id])
    WHERE b.[is_actief] <> 0 AND ((((EXISTS (SELECT 1                  FROM [COB].[Object_role] f WHERE b.[id] = f.[vastgoedlevel0_id]
    AND f.[gebruiker_id] = @p0                                      ) OR b.[klant_id] IS NOT NULL AND b.[klant_id] IS NULL                                  ) OR EXISTS
    (SELECT 1                  FROM [contract] g JOIN [COB].[vastgoedlevel0_contract] AS h ON (g.[contractnummer] = h.[contractnummer]) WHERE g.[inspectiebureau_id]
    IS NULL                                   AND g.[status] = @p1                                       AND g.[ingangsdatum] <= @p2 AND ((g.[einddatum] IS NULL OR
    g.[einddatum] > @p3)) AND h.[vastgoedlevel0_id] = b.[id])) OR b.[vastgoed_object_id] IS NOT NULL AND not ((ISNULL(c.[is_actief] , '') = '')) AND d.[is_actief] = 'Y'
    AND EXISTS (SELECT 1                  FROM [COB].[Object_role] i WHERE e.[id] = i.[vastgoedobject_id] AND i.[gebruiker_id] IS NOT NULL AND i.[gebruiker_id] = @p4                                      ))) ORDER BY COL53
    -------------------------- VASTGOEDLEVEL0 --------------------
    SELECT a.[id] AS COL1, a.[voa_class] AS COL2, a.[itk_gewenst] AS COL3, a.[itk_huidig] AS COL4, a.[aanmaakdatum] AS COL5, a.[actief_einddatum] AS COL6, a.[actief_startdatum]
    AS COL7, a.[addresshash] AS COL8, a.[alias] AS COL9, a.[partyId_assetManager] AS COL10, a.[asset_manager_enf] AS COL11, a.[partyId_assetOwner] AS COL12, a.[partyId_assetUser]
     AS COL13, a.[bebouwd] AS COL14, a.[beheerder] AS COL15, a.[beheerder_email] AS COL16, a.[beheerder_telefoon] AS COL17, a.[bouwaard] AS COL18, a.[bouwjaar] AS COL19, a.[bron]
     AS COL20, a.[bvo] AS COL21, a.[clustr] AS COL22, a.[complex] AS COL23, a.[eigendom] AS COL24, a.[eigendom_type] AS COL25, a.[ref_oracle_id] AS COL26, a.[gebouwfunctie_id]
      AS COL27, a.[gebouw_functie_enf] AS COL28, a.[grensbedrag_por] AS COL29, a.[huis_nummer] AS COL30, a.[inspectiebureau_id] AS COL31, a.[is_actief] AS COL32, a.[is_mef] AS
      COL33, a.[itk] AS COL34, a.[itkbrand] AS COL35, a.[itkenergetica] AS COL36, a.[itkinzicht] AS COL37, a.[itkonderhoud] AS COL38, a.[klant_id] AS COL39,
      a.[laatste_rgdboeiproces] AS COL40, a.[laatste_rgdboei_status] AS COL41, a.[laatste_wijziging_door] AS COL42, a.[land] AS COL43, a.[latitude] AS COL44,
      a.[longitude] AS COL45, a.[monument] AS COL46, a.[naam] AS COL47, a.[businesskey] AS COL48, a.[object_manager_enf] AS COL49, a.[object_visie_enf] AS COL50,
      a.[objectvisie] AS COL51, a.[onderdeel_complex] AS COL52, a.[onderhoudsniveau] AS COL53, a.[plaats] AS COL54, a.[planjaar] AS COL55, a.[postcode] AS COL56,
      a.[serviceovereenkomst] AS COL57, a.[status] AS COL58, a.[stoplichtblauw] AS COL59, a.[stoplichtdatum] AS COL60, a.[stoplichtgroen] AS COL61, a.[stoplichtoranje]
      AS COL62, a.[stoplichtrood] AS COL63, a.[straat] AS COL64, a.[uitvoeringsniveau] AS COL65, a.[vastgoed_object_id] AS COL66, a.[verwacht_rgdboeiproces] AS COL67,
      a.[verwijder_reden] AS COL68, a.[verwijderdatum] AS COL69, a.[wijzigingsdatum] AS COL70, b.[Buffercapaciteit] AS COL71, b.[ivginspecteur_gebruikerId] AS COL72,
      b.[object_code] AS COL73, b.[TechnischeCapaciteit] AS COL74, b.[vertrouwelijk] AS COL75, c.[Oppervlak_verhard] AS COL76
      FROM [COB].[vastgoedlevel0] a
         [COB].[gebouw] AS b ON (a.[id] = b.[id])
      LEFT JOIN [COB].[land] AS c ON (a.[id] = c.[id]) WHERE a.[is_actief] <> 0
      AND ((((EXISTS (SELECT 1                
       FROM [COB].[Object_role] d WHERE a.[id] = d.[vastgoedlevel0_id] AND d.[gebruiker_id] = @p0                                      )
       OR a.[klant_id] IS NOT NULL AND a.[klant_id] IS NULL                                  ) OR EXISTS (SELECT 1                 
       FROM [contract] e JOIN [COB].[vastgoedlevel0_contract] AS f ON (e.[contractnummer] = f.[contractnummer]) WHERE e.[inspectiebureau_id] IS NULL                                   AND e.[status] = @p1                                       AND e.[ingangsdatum] <= @p2 AND ((e.[einddatum] IS NULL OR e.[einddatum] > @p3)) AND f.[vastgoedlevel0_id] = a.[id])) OR EXISTS (SELECT 1234567 FROM [COB].[Vastgoed_Object] g WHERE a.[vastgoed_object_id] = g.[id] AND a.[vastgoed_object_id] IS NOT NULL AND not (EXISTS (SELECT 1234567 FROM [COB].[Vastgoed_Object] h WHERE a.[vastgoed_object_id] = h.[id] AND (ISNULL(h.[is_actief] , '') = ''))) AND g.[is_actief] = 'Y' AND EXISTS (SELECT 1                  FROM [COB].[Object_role] i WHERE g.[id] = i.[vastgoedobject_id] AND i.[gebruiker_id] IS NOT NULL AND i.[gebruiker_id] = @p4                                      )))) ORDER BY COL48
  2. Thomas
    Thomas avatar
    590 posts

    Posted 07 Oct 2015 Link to this post

    Hi Ben,

    looks like then the line
     predicateGebouwen = predicateGebouwen.Or(x => x.Rollen.Any(z => z.Gebruiker == g));

    is causing the inner join where you expected an outer join; right?

    What if you changed to
     predicateGebouwen = predicateGebouwen.Or(x => x.Rollen.Where(z => z.Gebruiker == g).Count() > 0);

    Please let me know if this change solves your issue.

    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. Ben
    Ben avatar
    8 posts
    Member since:
    Mar 2011

    Posted 09 Oct 2015 Link to this post

    Actually it is this part that makes three inner joins where it should create outer joins:

     predicateGebouwen = predicateGebouwen.Or(x => x.VastgoedObject != null && !string.IsNullOrEmpty(x.VastgoedObject.IsActief) && x.VastgoedObject.IsActief.Equals("Y", StringComparison.CurrentCultureIgnoreCase) && x.VastgoedObject.Rollen.Any(z => z.Gebruiker != null && z.Gebruiker == g));

    I changed it to:

    predicateGebouwen = predicateGebouwen.Or(x => x.VastgoedObject != null && !string.IsNullOrEmpty(x.VastgoedObject.IsActief) && x.VastgoedObject.IsActief.Equals("Y", StringComparison.CurrentCultureIgnoreCase) && x.VastgoedObject.Rollen.Where(z => z.Gebruiker != null && z.Gebruiker == g).Count() > 0);

    Still it makes three  inner joins  with VAstgoedObject



  4. Thomas
    Thomas avatar
    590 posts

    Posted 13 Oct 2015 Link to this post

    Hmm, then it looks like we have an invalid translation of the collection Rollen access.

    predicateGebouwen = predicateGebouwen.Or(x => x.VastgoedObject != null && !string.IsNullOrEmpty(x.VastgoedObject.IsActief) && x.VastgoedObject.IsActief.Equals("Y", StringComparison.CurrentCultureIgnoreCase) && x.VastgoedObject.Rollen.Where(z => z.Gebruiker != null && z.Gebruiker == g).Count() > 0);

    To get around this issue another (nonmatching) condition on the x variable seems to be needed:

    predicateGebouwen = predicateGebouwen.Or(x => (x.VastgoedObject != null && !string.IsNullOrEmpty(x.VastgoedObject.IsActief) && x.VastgoedObject.IsActief.Equals("Y", StringComparison.CurrentCultureIgnoreCase) && x.VastgoedObject.Rollen.Where(z => z.Gebruiker != null && z.Gebruiker == g).Count() > 0) || (x.ABC == DEF));

    Please use a property instead of ABC that does not have a value like DEF. At least in my tests here having this additional OR'd condition forced the runtime to generate EXISTS nodes instead of inner joins.

    Please get back to us in case this workaround is not doing its job.

    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  5. Ben
    Ben avatar
    8 posts
    Member since:
    Mar 2011

    Posted 13 Oct 2015 in reply to Thomas Link to this post

    Tried this:

    predicateGebouwen = predicateGebouwen.Or(x => (x.VastgoedObject != null && !string.IsNullOrEmpty(x.VastgoedObject.IsActief) && x.VastgoedObject.IsActief.Equals("Y", StringComparison.CurrentCultureIgnoreCase) && x.VastgoedObject.Rollen.Where(z => z.Gebruiker != null && z.Gebruiker == g).Count() > 0) || (x.VastgoedObject.BeheerderEmail == "#"));

    Still INNER JOINS and also I get sql timeouts on the query.


  6. Thomas
    Thomas avatar
    590 posts

    Posted 16 Oct 2015 Link to this post

    OK, we then need to digg deeper into this. Can you provide us with a model that shows the behavior?

    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top