The problem
I have two tables with a relation; I can get any data I want from one table or the other without any problem but I can't manage getting data from both together.
The environment:
- Visual Studio 2022
- Webassembly Blazor project running on .NET 6.0
- Nuget packages
- Microsoft.EntityFrameworkCore 6.0.1
- Pomelo.EntityFrameworkCore.MySql
- Database MariaDb 13.3.31
The way I've constructed the data environment:
With PhpMyAdmin, I created the 2 tables with the relation.
I then launched the scaffolding command in package manager console:
Scaffold-DbContext "Server=192.168.0.22;user=MyUser;Password=MyPassword;Database=MyDb;"
Pomelo.EntityFrameworkCore.MySql
-OutputDir Models -Context "MySqlstockDataContext" -DataAnnotations
After that, I created the controller with the VS2022 command "Add new Controller" and selected the "API controller with actions using Entity Framework"
My models, DbContext and controllers were built by the tools of VS 2022, I didn't write any code for this.
The database (a simple one with articles which can have movements (Input or Output of stock):
This is the generated code for the model class "Articles":
We can see than there are no propositions of properties coming from the "article" table.
To be sure my question were clear, let me tell what result I try to have : I'd like to get all the properties of the "mouvement" table and the "nom_article" property (and others also) corresponding in the "article" table. In SQL I think it would correspond to : SELECT * FROM mouvement INNER JOIN article ON mouvement.id_article = article.id_article; that would allow to finally get the properties to display a table like this :
id_mvt|id article|type_mvt|Qte_mvt|date_mvt|nom_article
Best regards.
CodePudding user response：
"e.IdArticleNavigation.NomArticle" is invalid inside an include.
You're supposed specify a navigation property inside an Include, not a simple property
One Article has many Movement. If you want to access an Article from a Movement you use its nav prop:
myMovement.IdArticleNavigation //an article
EF performs joins when it sees you navigate around the objects graph:
context.Movements.Select(m => new { m.MovementName, m.IdArticleNavigation.ArticleName });
Because you've accessed a property of a related entity in a Select, EF will automatically create a JOIN in the sql to get the related Article for each Movement so it can access the related data
It does a similar thing if you navigate around in a Where clause
We use Include to tell EF to perform JOINs even if we don't use the related data in the where/select. It's like saying "I want all the movements where... and their related articles because later in the client side code I will use some article properties"
But be clear: you don't specify the properties of an article that you later want to use, you just specify the article and EF will download the whole thing
var helloMovement = context.Movements
.Include(m => m.IdArticleNavigation)
.First(m => m.MovementName == "hello");
Without the Include, helloMovement.IdArticleNavigation would be null
If you want only certain props (for example you know that every Article has a 5 megabyte json column and you don't want to download it) you can use the anonymous type pattern I mentioned above in the "navigating around in a select" - the generated sql in that case will only mention the article name and movement name
Edit
We can see than there are no propositions of properties coming from the "article" table.
I'm starting to understand the disconnection between the way you view tables, SQL, relations, EF and object graphs
To be sure my question were clear, let me tell what result I try to have : I'd like to get all the properties of the "mouvement" table and the "nom_article" property (and others also) corresponding in the "article" table.
In SQL I think it would correspond to : SELECT * FROM mouvement INNER JOIN article ON mouvement.id_article = article.id_article;
Yes, but what you need to appreciate is that SQL produces rectangular blocks of data and EF/C# produces object graphs that are more like a tree
Let's have some simple SQL tables:
People
Id, Name, Age
1, John, 27
2, Mary, 23
Skills
Id, PersonId, Description
1, 1, Stonework
2, 1, Welding
3, 2, Carpentry
4, 2, Plumbing
If you join these you get a rectangle block of data
Id, Name, Age, Id, PersonId, Description
1, John, 27, 1, 1, Stonework
1, John, 27, 2, 1, Welding
2, Mary, 23, 3, 2, Carpentry
2, Mary, 23, 4, 2, Plumbing
This is what SQL does - it repeats the people rows because they each have 2 skills
EF doesn't do this. You have some Person that has a List of Skills:
{ name: John, age: 27, skills: [ { description: stonework }, { description: welding } ] }
{ name: Mary, age: 23, skills: [ { description: carpentry }, { description: plumbing } ] }
It's more like a tree, or a graph. There is one person object and there are two skills objects. One of the things EF has to do if it does joins is dedupe all the multiple Johns that come back so you only get one Person, and the Person has 2 Skills. Each Skill links back to the Person too so you could have a Person who has a Skill and the Skill has a Person (the same Person as we started with) ..
You could write a code like:
myPerson.Skills.First().Person.Skills.First().Person.Skills.First().Person.Skills.First().Person.Skills.First().Person.Skills.First().Person.Skills.First().Person.Skills.First().Person.Skills.First();
The graph just goes round and round. Skills is a List, the First entry is a Skill, that has a Person, that has Skills, the first of which is...
EF's job is to watch you navigating around this graph of linked entities and form SQLs that will allow it to retrieve data to populate the graph to support your navigating of it
When you do:
context.People.Where(p => p.Name == "John");
You get John. His Skills list will be empty, even if he has Skills in the DB. EF didn't see you use Skills so it doesn't bother joining it in. It makes the minimum number of joins it has to to pull just the data you seem to want to use
If you said:
context.People.Where(p => p.Skills.Any(s => s.Description== "Carpentry"));
EF would do the join (actually, it'd probably use an EXISTS, but let's pass over that for now) so it can get the WHERE to work (it needs the related data) but it wouldn't select the Skills, because again it doesn't look like you need them
If you mentioned in a Select:
context.People.Select(p => new { p.Name, p.Skills });
EF can see you using Skills, so it'll JOIN it so it can deliver you what you've asked for (an anonymous type of the person name and their Skills)
If you didn't mention Skills:
context.People.Select(p => p);
Then you'd get all the People, but all their Skills collections would be empty, because EF can't see you wanting to use them
If you told EF you wanted the Skills too (because you plan to use them some other time):
context.People.Include(p => p.Skills);
Then you'd get all the People objects and their Skills collections would be populated with skills
Now let's go back to the rectangular blocks of data in SQL, and the graphs in EF
SELECT * FROM people JOIN skills on people.Id = skills.peopleId
Yes, joining skills and people will let you "just use the names of skills/people in the select" :
SELECT name, Description FROM people JOIN skills on people.Id = skills.peopleId
but really you're supposed to use the table name (or alias), so SQL still has this notion of "remembering where the data came from":
SELECT people.name, skills.Description FROM people JOIN skills on people.Id = skills.peopleId
Object graphs in C# don't let you just "type random property names" - you have to "specify where the data comes from"
myPerson.Skills.First().Description.Substring(2);
Now, quick chat about LINQ inputs and outputs
context.People //People is a list of Person
.Where(p => p.Name == "John") //Where outputs a list of Person
.Where(p => p.Age == 27) //Where outputs a list of Person
Some things in LINQ change what you get out, others don't. When you chain method calls like A.B.C.D you have to bear in mind that what A outputs is what is fed into B, and what B outputs is what is fed into C.
Some things output a similar thing to what is fed in:
Where - called on a list of X, produces a list of X
Some things slightly change what comes out:
First - called on a list of X, produces a single X
Some things drastically change what comes out:
Select - called on a list of X, produces a list of Y
Include is one of those things that doesn't change what comes out. You have:
context.Skills //a list of Skill
.Include(s => s.Person) //essentially still a list of Skill
Including the Person doesn't magically cause some different kind of object to come out, from the list of Skills that were fed in. When you say Select after Include you're still selecting from a list of skills
context.Skills //a list of Skills
.Include(s => s.Person) //essentially still a list of Skill
.Select(s => ... //s is a Skill
For every Skill in the list, if you want to reach to the Person with the skill, you have to navigate there:
context.Skills //a list of Skills
.Include(s => s.Person) //essentially still a list of Skill
.Select(s => s.Person.Name)//s is a Skill, with a Person property that is a Person, that has a Name property
You have to do this, because this is how object graphs work in C# - they don't work like that possibility in SQL where you can just start mentioning names of columns after joining them in; you always have to be specific how you get to some property in C#
You could create an anonymous type that has all the properties you want:
context.Skills
.Include(s => s.Person)
.Select(s => new { PersonName = s.Person.Name, SkillDesc = s.Description})
This produces a new object, not a person, not a skill, but a temporary holder with just a string PersonName and a string SkillDesc properties. If you fed that into another Select, then you you would be looking at a changed set of objects that have those properties (and all the other properties of your Person/Skill are lost)
context.Skills
.Include(s => s.Person)
.Select(s => new { PersonName = s.Person.Name, SkillDesc = s.Description})
.Select(at => at.PersonName)
This is like doing the following SQL:
SELECT PersonName
FROM
(
SELECT people.name as PersonName, skills.Description as SkillDesc
FROM people JOIN skills on people.Id = skills.peopleId
) at
that would allow to finally get the properties to display a table like this :
id_mvt|id article|type_mvt|Qte_mvt|date_mvt|nom_article
Hopefully you're now up on what you need to do.. When you start from
context.Movements
and you let EF see you using properties from Article:
context.Movements
.Where(m => ...) //Movements in, Movements out
.Select(m => new { //Movements in, new anonymous object out
m.IdMvt,
m.IdArticle,
...
m.IdArticleNavigation.NomArticle
});
Remember; there's no Include necessary here, because you're selecting the related data (but you have to navigate to it via a Movement!)
You could also do:
var x = context.Movements
.Include(m => m.IdArticleNavigation)
.Where(m => ...) //Movements in, Movements out
.ToList(); //Movements in, Movements out
This produces a list of movements with fully populated IdArticleNavigation properties which are the related Articles. You could then do
x.First() //a Movement
.IdArticleNavigation //the Article related to the first Movement
.Nom //the name of the Article
CodePudding user response：
Caius, thank you for the time taken for me and for all those explainations. It seems to me I can understand them but unfortunately, i still can't get the datas from the two tables. I've tried many solutions but none of them works (with error or not in Visual Studio). According to your explainations, I think this should work to get the complete list of mouvements with the corresponding name of article (nom_aticle) for each mouvement :
public async Task<ActionResult<IEnumerable<Mouvement>>> GetMouvements()
{
return await _context.Mouvements
.Include(e => e.IdArticleNavigation)
.Select(e => e.IdArticleNavigation.NomArticle)
.ToListAsync();
}
This code gives an error in VS (translated from french -my language- to english) : Impossible to convert implicitly the type System.Collectons.Generic.List <String in Microsoft.AspNetCore.Mvc.ActionResult<System.Collections.IEnumerable<ligprod.Server.Models.Mouvement>>.
The mouvement models was published as a picture in my first demand (link "Screenshot of Model Class Mouvements").
Regards.