This question is locked. New answers and comments are not allowed.
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
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
0
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
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
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
Hmm, then it looks like we have an invalid translation of the collection Rollen access.
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
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
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
OK, we then need to digg deeper into this. Can you provide us with a model that shows the behavior?
Regards,
Thomas
Telerik
Regards,
Thomas
Telerik
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.