This is a migrated thread and some comments may be shown as answers.
ORM problem with Linq to SQL
5 Answers 6 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Ben
Top achievements
Rank 1
Ben asked on 05 Oct 2015, 10:59 AM
We have a couple of tables:
 VASTGOEDLEVEL0
     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
  LEFT JOIN
     [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

5 Answers, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 07 Oct 2015, 04:04 PM
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.

Regards,
Thomas
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
0
Ben
Top achievements
Rank 1
answered on 09 Oct 2015, 12:15 PM

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

 

 

0
Thomas
Telerik team
answered on 13 Oct 2015, 08:45 AM
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.

Regards,
Thomas
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
0
Ben
Top achievements
Rank 1
answered on 13 Oct 2015, 12:40 PM

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.

 

0
Thomas
Telerik team
answered on 16 Oct 2015, 12:01 PM
OK, we then need to digg deeper into this. Can you provide us with a model that shows the behavior?


Regards,
Thomas
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
LINQ (LINQ specific questions)
Asked by
Ben
Top achievements
Rank 1
Answers by
Thomas
Telerik team
Ben
Top achievements
Rank 1
Share this question
or