Query with related data in the Entity Framework

Question

I am trying to bring data of the materials with the data of your relationships with the Unit and TD tables, 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 results. 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'~'

Answer 1

Diego and Gypsy Morrison, I found the error: it was an error in the table of Unidade in the database. By default the Entity Framework will do the Inner Joins for the browsing properties, in my case the UnidadeID and the TDID, but I need the association to be made by another table field, the 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 why the data of these two tables comes from another system and are imported via Integration Services, and associations should be made by codes that are in the other system.

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

Thank you.

Answer 2

Try this:

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

Don’t forget to import the reference:

using System.Data.Entity;