Include on 1->n relation with EF core 5 doesn't work

[mciprijanovic]

Hi. Here is the issue I found. Lets consider the simplest model with 2 classes: Parent and Child. Bidirectional relationship between them says that parent has a list of children and each child has one parent. I have the latest tool version 3.0.2.0 and EF core 5.0.1. Following statement does not return children:
var parents = dbContext.Parents.Include(x => x.Children);

Children collection is always empty although SQL profiler says that the query is good. If relation is changed to 0->n in the tool or EF core is set to 2.1, all works as expected.

[ab-tools]

Yes, I can subscribe to this issue:
Despite in the MySQL database itself "1 to n" related entities are available the corresponding children collection in the .NET entity simply stays empty.

Looks like I can also reproduce this issue with EF Core 5.0.0.

In case you need any further information from us on this, Michael, just let us know!

[ab-tools]

Sorry to bother you, @michael-sawczyn, but did you have the chance to try that on your side?

I'm just wondering if I'm doing something wrong as this is such basic functionality, so I would expect more reports about this issue actually.

Thanks for support as always
Andreas

[msawczyn]

I've actually run across this in one of my projects as well. Am trying to track it down. Stay tuned!

[ab-tools]

Great to hear that you can reproduce this as well, @msawczyn - really curious what you can find out!

Please let me know if I can be any help with testing a new pre-release or so.

Thanks a lot
Andreas

[ab-tools]

Thanks for the feedback, Michael!

I've got that issue with MySQL, so it's definitely not specific to SqLite.

Unfortunately also my project here is pretty complex, but I will try to see if I can reproduce it also with a minimal project - will try that directly - let you know shortly!

[ab-tools]

I am able to reliably reproduce this also with a smaller project which I just sent to you via e-mail (michaelsawczyn[at]gmail[dot]com).

Please let me know if you need any further information!

Thanks
Andreas

[mciprijanovic]

Michael, I can't manage not to reproduce it 😀. Whenever I have 1 to many relation and use include I get related elements not filled with data. It happens the same on SqlServer and MySql. In my mysql project I could change relations to 0 to many and then it works, but in SqlServer project I can not do that since the project is mature.
Because of this I am still keeping EF 2.1. since this happens only on EF 5.x
Waiting for update to migrate project to EF 5

[msawczyn]

In your projects, have you enabled lazy loading or are you explicitly adding an Include statement to your queries?

[ab-tools]

Did you receive my example project via e-mail, Michael?

No lazy loading enabled and as you can see in the very simple example queries there is also no Include used.

[msawczyn]

The only files I got from you were for the collation settings. This issue is regarding 1..n associations. :-)

[ab-tools]

Hm, I sent you a file via e-mail two days ago (on Saturday) with an example project - didn't you receive that?

The e-mail subject was "EFCore Sample Project", perhaps it went into spam on your Google Mail account?

[msawczyn]

Please see the following:
ConsoleApp1.zip

This is a simple console app with two classes: Master and Detail, and a 1..N association between them. For portability, I've used LocalDB as the storage. Running the application will create the database and tables using the code-first migrations.

Initially, no lazy loading is configured. You'll see that, with no .Include() calls, no Detail objects are returned.

Uncommenting one of the lines in the Context partial will cause the association to be lazy loaded - the other can be left commented. Both are supplied just as alternatives and tests to ensure that the scenario works in either case. Running the app after uncommenting one of them shows that both queries (with or without .Include() calls) does indeed return the associated objects.

@mciprijanovic and @ab-tools , could you please run these in your environment to make sure that there isn't anything special about my environment that's making this work and let me know the results?

Thanks.

[ab-tools]

Testing with SQL Server is not that quick for me as I don't even have that installed (thus also no LocalDB available), but changing your example to MySQL was simple, of course.

But I can confirm that with your example in all cases (with or without lazy loading by enabling the first or second commented line) I always get 3 associated objects returned using MySQL.

Really not sure what's the difference of the model I sent to you where I can reproduce the problem.

Can you confirm, Michael, that you have received my e-mail from Saturday with the file "EFCoreTest.zip" attached?
I would be very curious if you can reproduce the problem on your side with my example.

[msawczyn]

Sorry. No files on Saturday.

[ab-tools]

Strange, just forwarded you the e-mail again, this time without attachment, but an OneDrive link instead - did you receive it this time?

[mciprijanovic]

In your projects, have you enabled lazy loading or are you explicitly adding an Include statement to your queries?

Lazy loading disabled, using Include.

[mciprijanovic]

ould you please run these in your environment to make sure that there isn't anything special about my environment that's making this work and let me know the results?

Hi Michael. Your solution works because you use uniderectional relation. Use bidirectional and It will not work anymore. :)
I changed only that and connection string to use my SQL server. OK, I also couldn't use script you provided in a code and migrations but I used context.Database.EnsureCreated() in order to create the db, but that is not the issue. Only thing that matters is bidirectional relation.

[ab-tools]

Good catch, @mciprijanovic, didn't notice that it was unidirectional!
But yeah, when I change it to bidirectional I can also reproduce the problem with the example.

@msawczyn, do you also see the problem on your side when you try it with a bidirectional 1..N association?

[ab-tools]

@msawczyn, sorry to bother, but did you had the chance to give that a try yourself in the meantime?
I would be really curious if you can reproduce this problem as well now...

[msawczyn]

I think I have the answer. I've pushed it out to /dist but it needs more testing, which will happen over the weekend.

[ab-tools]

Thanks, @msawczyn, just gave it a quick try and this is actually looking pretty good to me on first sight! :-)

I'll do some more tests tomorrow.

[mciprijanovic]

@michael-sawczyn, reported bug still exists for .NET Core 3.x :( It is fixed for 5.x, already worked for 2.x but since we couldn't go to 5.x because of the Azure functions limitations, we had to choose EF core 3.0.10. When I tried the example already written it failed. :( So, still postponing migration to prod.
Is it easy to fix it since you already done it for 5.x? To remind: when I have bidirectional master-detail relationship, I disable lazy loading, and when I select master-> include details, I get empty details list.

[msawczyn]

There is an issue with the designer improperly mapping the selected EF version to capabilities. That's (hopefully) fixed in 3.0.4, whose RC1 is available at https://github.com/msawczyn/EFDesigner/blob/master/dist/Sawczyn.EFDesigner.EFModel.DslPackage.vsix

If you could give that a try, I'd be in your debt.

[mciprijanovic]

There is an issue with the designer improperly mapping the selected EF version to capabilities. That's (hopefully) fixed in 3.0.4, whose RC1 is available at https://github.com/msawczyn/EFDesigner/blob/master/dist/Sawczyn.EFDesigner.EFModel.DslPackage.vsix

If you could give that a try, I'd be in your debt.

Just tried on vsix double click. Error: "The file is not valid vsix package." Log shows this:

2/12/2021 2:35:34 PM - Microsoft VSIX Installer
2/12/2021 2:35:34 PM - -------------------------------------------
2/12/2021 2:35:34 PM - vsixinstaller.exe version:
2/12/2021 2:35:34 PM - 16.8.3036
2/12/2021 2:35:34 PM - -------------------------------------------
2/12/2021 2:35:34 PM - Command line parameters:
2/12/2021 2:35:34 PM - C:\Program Files (x86)\Microsoft Visual Studio\Installer\resources\app\ServiceHub\Services\Microsoft.VisualStudio.Setup.Service\VSIXInstaller.exe,C:\Users\milos\Downloads\Sawczyn.EFDesigner.EFModel.DslPackage.vsix
2/12/2021 2:35:34 PM - -------------------------------------------
2/12/2021 2:35:34 PM - Microsoft VSIX Installer
2/12/2021 2:35:34 PM - -------------------------------------------
2/12/2021 2:35:34 PM - Skipping product Microsoft.VisualStudio.Product.BuildTools (0a65f4fc) since it does not support extensions
2/12/2021 2:35:34 PM - Initializing Install...
2/12/2021 2:35:34 PM - Microsoft.VisualStudio.ExtensionManager.InvalidExtensionPackageException: The file is not a valid VSIX package. ---> System.IO.FileFormatException: File contains corrupted data.
at MS.Internal.IO.Zip.ZipIOEndOfCentralDirectoryBlock.FindPosition(Stream archiveStream)
at MS.Internal.IO.Zip.ZipIOEndOfCentralDirectoryBlock.SeekableLoad(ZipIOBlockManager blockManager)
at MS.Internal.IO.Zip.ZipArchive..ctor(Stream archiveStream, FileMode mode, FileAccess access, Boolean streaming, Boolean ownStream)
at MS.Internal.IO.Zip.ZipArchive.OpenOnStream(Stream stream, FileMode mode, FileAccess access, Boolean streaming)
at System.IO.Packaging.ZipPackage..ctor(Stream s, FileMode mode, FileAccess access, Boolean streaming)
at System.IO.Packaging.Package.Open(Stream stream, FileMode packageMode, FileAccess packageAccess, Boolean streaming)
at Microsoft.VisualStudio.ExtensionManager.InstallableExtensionImpl.ReadVSIXManifestFromPackage(Stream stream, CultureInfo preferredCulture)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.VisualStudio.Telemetry.WindowsErrorReporting.WatsonReport.GetClrWatsonExceptionInfo(Exception exceptionObject)
--- End of inner exception stack trace ---
at Microsoft.VisualStudio.ExtensionManager.InstallableExtensionImpl.ReadVSIXManifestFromPackage(Stream stream, CultureInfo preferredCulture)
at Microsoft.VisualStudio.ExtensionManager.InstallableExtensionImpl..ctor(String path, CultureInfo preferredCulture)
at Microsoft.VisualStudio.ExtensionManager.ExtensionEngineImpl.CreateInstallableExtension(String extensionPath, CultureInfo preferredCulture)
at Microsoft.VisualStudio.ExtensionManager.ExtensionEngineImpl.CreateInstallableExtension(String extensionPath)
at VSIXInstaller.ExtensionService.GetInstallableData(String vsixPath, String extensionPackParentName, Boolean isRepairSupported, IStateData stateData, IEnumerable1& skuData) at VSIXInstaller.ExtensionPackService.IsExtensionPack(IStateData stateData, Boolean isRepairSupported) at VSIXInstaller.ExtensionPackService.ExpandExtensionPackToInstall(IStateData stateData, Boolean isRepairSupported) at VSIXInstaller.App.Initialize(Boolean isRepairSupported) at VSIXInstaller.App.Initialize() at System.Threading.Tasks.Task1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()

[msawczyn]

Most odd. How about https://github.com/msawczyn/EFDesigner/raw/master/dist/Sawczyn.EFDesigner.EFModel.DslPackage.vsix ?

[mciprijanovic]

Most odd. How about https://github.com/msawczyn/EFDesigner/raw/master/dist/Sawczyn.EFDesigner.EFModel.DslPackage.vsix ?

Michael, this package works. I upgraded tool to 3.0.4.5, however, behaviour is the same. EF 2.2.6 and for example 5.0.3 work, but 3 or 3.1.10 not. Empty children list is what I get.

[mciprijanovic]

@msawczyn, seems the bug is fixed in the new version you published: 3.0.4.7. I tried EF core 2.2.6, 3.1.2 and 5.0.3. Thanks, we can finnaly go to the new version of EF core in our project. Can you just confirm it? Thanks.

[msawczyn]

One of the issues that release was targeted to fix was exactly this one. Glad to hear it's working for you.