Query with related data in the Entity Framework

Question

Query with related data in the Entity Framework

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

I'm trying to get data from the materials with the data of your relationships with the tables Unit and TD, but this code below does not return anything, if I do without Include () it returns the data but with the relationship does not bring any result . In the Material class I have the navigation properties for Unit and TD.

    public IEnumerable<Material> Listar(String Nome)
    {
        IEnumerable<Material> materiais = (from x in db.Material
                                           .Include("Unidade")
                                           .Include("TD")
                                           .Where(x => x.Nome.Contains(Nome)) select x).ToList();                               
        return materiais;
    }

SQL code generated by Entity:

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[CodTron] AS [CodTron], 
[Extent1].[RefTron] AS [RefTron], 
[Extent1].[Nome] AS [Nome], 
[Extent1].[UnidadeID] AS [UnidadeID], 
[Extent1].[TDID] AS [TDID], 
[Extent2].[ID] AS [ID1], 
[Extent2].[CodTron] AS [CodTron1], 
[Extent2].[Referencia] AS [Referencia], 
[Extent2].[Descricao] AS [Descricao], 
[Extent2].[Simbolo] AS [Simbolo], 
[Extent3].[ID] AS [ID2], 
[Extent3].[Numero] AS [Numero], 
[Extent3].[Descricao] AS [Descricao1], 
[Extent3].[RefTron] AS [RefTron1]
FROM   [dbo].[Material] AS [Extent1]
INNER JOIN [dbo].[Unidade] AS [Extent2] ON [Extent1].[UnidadeID] = [Extent2].[ID]
INNER JOIN [dbo].[TD] AS [Extent3] ON [Extent1].[TDID] = [Extent3].[ID]
WHERE [Extent1].[Nome] LIKE @p__linq__0 ESCAPE N'~'

c# entity-framework-6 asp.net-mvc

asked by anonymous 20.08.2014 / 16:33

2 answers

2

Try this:

IEnumerable<Material> materiais = db.Material
                                       .Include(u=>u.Unidade)
                                       .Include(t=>t.TD)
                                       .Where(x => x.Nome.Contains(Nome)).ToList();

Do not forget to import the reference:

using System.Data.Entity;

20.08.2014 / 16:39

Select to subtract current value from previous and return column with result Clear a datagridview in C #

score 3 · Accepted Answer

Diego and Cigano Morrison, I found the error: it was an error in the Unidade table in the database. By default the Entity Framework will do the Inner Joins for the navigation properties, in my case the UnidadeID and the TDID , but I need the association to be made by another field of the table, CodTron , that is, the correct comparison is Unidade.MaterialID = Unidade.CodTron and in the TD table the purchase would be Materia.TDID = TD.CodTron , this happens in my case because the data of these two tables comes from another system and they are imported via Integration Services , and the associations must be made by codes that are in the other system.

Because of this error it ends up making no reference to data in the table of Unidade so it does not bring the data. After @Cyano spoke to put the Entity generated code in the Management Studio I saw that even replacing the parameter with the name of the material it did not bring the data, so I started to investigate the database and found the error.

Thank you.