Select request on nullable foreign key #6301
Comments
|
I someone need it : Something like that : will generate the correct query EXCEPT that take(10) is on client side ... Generate a CROSS JOIN request instead of a left outer join, but it's process the take(50) in the request. |
|
@DSorin does this also happen if you query just for the brand name, like so?: var qsd = _context.AncdAnalyseCommandesDetail
.Include(i => i.Product).ThenInclude(pro => pro.Brand)
.Select(s => new
{
ProductBrandName = s.Product.Brand.BrandName
}).Take(100000).ToList();if so, could you share code for relevant entities: AncdAnalyseCommandesDetail, Product, Brand and dbcontext (or more specifically contents of OnModelCreating used to configure those 3 entities above)? ProductBrandName = s.Product != null ? s.Product.Brand.BrandName : null Some extra info: you don't actually need to put Include calls in your query, EF will figure out which navigations it needs based on the query itself - the Includes are actually ignored, because no entities are materialized, just their individual properties. As to the solution you proposed, using groupjoin is doing client eval because of: #4588 |
|
Hi maunar,
I do not get the error, but the take is client side + it's performing a "select * from...." kind of request : And the left outer join is correctly generated. My table : ANCD_analyse_commandes_details contains 5 483 944 records so i cannot perform neither a "select *" or making the .take on client side. I use Scaffold-DbContext for generate all my context, from a SQL2012 database => I zipped you a console App with the relevant file in it. It's only the 3 tables here, if you need the entire context let me know ~~~ |
|
@DSorin since the issue doesn't repro in isolation, I will need the entire context (and entities). We can do it over email if that's more convenient for you. My address is [my_github_username][at]microsoft[d0t]com. Also, once this bug is fixed, you will still get select * and filtering/paging done on the client, due to #4588 - we lose track of everything after first optional navigation. That bug is scheduled to be fixed for the next release, as many people are in similar situation to yours. |
|
@DSorin when/if you send me a repro, please also post something here. I want to make sure my spam filter won't eat email from outside the organization :) |
|
Hi maumar, You will find attached the entire context, this is a database existing since early 2000' so a lots of people worked on it. For the best and the worst ! My guest is, if you can manage all the trap in there the Framework should be quite stable. You will notice that when using a "single block" context, initialize time for the first request when you validate the model is almost >10 secondes ... FYI : 2/ Here the kind of request i'm working with, ( mostly when i'm doing DashBord and reporting ). The code below work fine in EF6 so i would expect the same of EFcore if i want to use it on our software. |
|
@DSorin I'm still unable to repro this. Used the project you provided and the queries above - getting no exception, and getting following sqls (i collapsed individual projections into *, for brevity) query form the project: SELECT [i].*, [i.AncdPro].*, [i.AncdPro.ProMar].*
FROM [ANCD_analyse_commandes_detail] AS [i]
INNER JOIN [PRO_Produits] AS [i.AncdPro] ON [i].[ancd_pro_id] = [i.AncdPro].[pro_id]
LEFT JOIN [MARQ_Marques] AS [i.AncdPro.ProMar] ON [i.AncdPro].[pro_mar_id] = [i.AncdPro.ProMar].[marq_id]
ORDER BY [i.AncdPro].[pro_mar_id](this projects everything and Take(100) is done on the client due to #4588 CliProAll: SELECT [client].[cli_id], [produit].[pro_id]
FROM [CLI_Clients] AS [client]
CROSS JOIN [PRO_Produits] AS [produit]
WHERE EXISTS (
SELECT 1
FROM [UCD_UniversClientsDetails] AS [a]
WHERE ([a].[ucd_uc_id] = 1) AND ([a].[ucd_cli_id] = [client].[cli_id])) AND EXISTS (
SELECT 1
FROM [UPD_UniversProduitsDetails] AS [a0]
WHERE ([a0].[upd_up_id] = 2) AND ([a0].[upd_pro_id] = [produit].[pro_id]))commandes: SELECT [ancd].*, [anc].*, [anp].*
FROM [ANCD_analyse_commandes_detail] AS [ancd]
INNER JOIN [ANC_analyse_commandes] AS [anc] ON [ancd].[ancd_anc_id] = [anc].[anc_id]
INNER JOIN [ANP_Analyse_Periode] AS [anp] ON ([anc].[anc_anp_id] = [anp].[ANP_id]) AND (2 = [anp].[ANP_type])
WHERE ((EXISTS (
SELECT 1
FROM [UCD_UniversClientsDetails] AS [a]
WHERE ([a].[ucd_uc_id] = @__filterldcUcId_0) AND ([a].[ucd_cli_id] = [anc].[anc_cli_id])) AND EXISTS (
SELECT 1
FROM [UPD_UniversProduitsDetails] AS [a0]
WHERE ([a0].[upd_up_id] = @__filterldcUcId_1) AND ([a0].[upd_pro_id] = [ancd].[ancd_pro_id]))) AND ([anp].[ANP_date_debut] >= @__filter_FilterDateDebut_2)) AND ([anp].[ANP_date_fin] <= @__filter_FilterDateFin_3)
ORDER BY [ancd].[ancd_pro_id], [anc].[anc_cli_id]assortiments: SELECT [pro].[pro_id], [cli].[cli_id], CASE
WHEN [astc].[ASTC_NumClass] = 1
THEN COALESCE([astd].[ASTD_Class1], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 2
THEN COALESCE([astd].[ASTD_Class2], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 3
THEN COALESCE([astd].[ASTD_Class3], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 4
THEN COALESCE([astd].[ASTD_Class4], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 5
THEN COALESCE([astd].[ASTD_Class5], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 6
THEN COALESCE([astd].[ASTD_Class6], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 7
THEN COALESCE([astd].[ASTD_Class7], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 8
THEN COALESCE([astd].[ASTD_Class8], N'Z') ELSE CASE
WHEN [astc].[ASTC_NumClass] = 9
THEN COALESCE([astd].[ASTD_Class9], N'Z') ELSE N'Z'
END
END
END
END
END
END
END
END
END
FROM [CLI_Clients] AS [cli]
INNER JOIN [ASTD_AssortData] AS [astd] ON [cli].[cli_assorID] = [astd].[ASTD_IdAssort]
INNER JOIN [PRO_Produits] AS [pro] ON [astd].[ASTD_IDProduit] = [pro].[pro_id]
INNER JOIN [SSEG_Sous_Segment] AS [sseg] ON [pro].[pro_sseg_id] = [sseg].[sseg_id]
INNER JOIN [ASTC_AssortClient] AS [astc] ON ([sseg].[sseg_seg_id] = [astc].[ASTC_IdSegment]) AND ([cli].[cli_id] = [astc].[ASTC_IdClient])
WHERE EXISTS (
SELECT 1
FROM [UCD_UniversClientsDetails] AS [a]
WHERE ([a].[ucd_uc_id] = 1) AND ([a].[ucd_cli_id] = [cli].[cli_id])) AND EXISTS (
SELECT 1
FROM [UPD_UniversProduitsDetails] AS [a0]
WHERE ([a0].[upd_up_id] = 1) AND ([a0].[upd_pro_id] = [pro].[pro_id]))prodReleves: SELECT [relp].*, [rel].*, [relf].*
FROM [RELP_Releve_Produit] AS [relp]
INNER JOIN [REL_Releve] AS [rel] ON [relp].[relp_rel_id] = [rel].[rel_id]
INNER JOIN [RELF_Releve_Fdv] AS [relf] ON ([rel].[rel_id] = [relf].[relf_rel_id]) AND (@__fdvId_0 = [relf].[relf_fdv_id])
WHERE (((([rel].[rel_type] = 1) AND ([rel].[rel_date] >= @__filter_FilterDateDebut_1)) AND ([rel].[rel_date] <= @__filter_FilterDateFin_2)) AND EXISTS (
SELECT 1
FROM [UCD_UniversClientsDetails] AS [a]
WHERE ([a].[ucd_uc_id] = @__filterldcUcId_3) AND ([a].[ucd_cli_id] = [rel].[rel_cli_id]))) AND EXISTS (
SELECT 1
FROM [UPD_UniversProduitsDetails] AS [a0]
WHERE ([a0].[upd_up_id] = @__filterldcUcId_4) AND ([a0].[upd_pro_id] = [relp].[relp_pro_id]))
ORDER BY [rel].[rel_cli_id], [relp].[relp_pro_id]I tried it on current bits as well as released version and seeing similar results. What version of EF core are you using? If you run the query from the project you zipped (using that model you provided) against your actual database, you see the exception? The data itself shouldn't really matter - the error you are seeing is in the query compilation phase, so I'm really curious whats the difference here. |
|
@maumar , Yes i still get the exception with the console app I provided. AND the log :
After reading your message i tried to played a little with my data, and => But if i move the "ProductBrandName = s.AncdPro.ProMar.MarqLibelle," as last in the anonymus select I got one query generated ! So there a select * on PRO_Produits and MARQ_Marques but not on other table. My guest would be, since we got 2 table with closed named "MAR_Marche" and "MARQ_Marques" Hope it's help you |
|
@DSorin thanks for the extra info, now I was able to figure it out what's going on. Issue you are seeing has been fixed after RTM has been released: As to the query that works for you and why some entities are projecting all the columns, MARQ_Marques is projected in its entirety because of this issue: #4588 PRO_Produits is materialized because when we join Produits with Marques, we generate the following query fragment first: {join MarqMarques s.AncdPro.ProMar in value(EntityQueryable`1[MarqMarques]) on IIF(([s.AncdPro] != null), Property([s.AncdPro], "ProMarId"), null) equals Convert(Property([s.AncdPro.ProMar], "MarqId")) into IEnumerable`1 s.AncdPro.ProMar_group}We add null protection logic for [s.AncdPro] element before accessing its property "ProMarId". Sometimes this is necessary, e.g. the outer collection is a subquery (and you don't know if there are any nulls in the collection or not - consider example in #6429). When we add the null protection logic, we need to know if a given object is null, and for that we currently need to materialize the object entirely also. But in this case null-ref won't happen so we should be smarter about it and only add it when it's really needed. I will address this in #6429. As for your situation I would recommend using as few optional navigations as you can, until #4588 is fixed (scheduled to be fixed in 1.1.0 currently, lots of people are hitting it). Since your database has large number of rows you will get pretty bad performance in any query with optional navigation, if that query has any sort of filter/paging. |
|
closing this as the issues have either been fixed in our current codebase or are tracked elsewhere |
maumar
added
closed-fixed
ajcvickers
added
type-unknown
and removed
closed-fixed
I want to perform a select on a nullable foreign key to build some anonymous type without having to retrieve all the field in database =>
Here the Product's Brand can be null, I always get an exception.
I tried some workaround without success :
It's working, but my product object is completely initialized so it's not performance wise.
C# ProductBrandName = s.Product.Brand.BrandName ?? ""won't work
If I remove the Brand part, the SQL request is correctly generated, and I'm under 1 second to generate my 100 000 item list so I would like to keep it that way ^^
(I translated the bold part of my code which is in French, so the stack trace do not match exactly!)
Any suggestion ?
The text was updated successfully, but these errors were encountered: