Linq filttar elements of LEFT JOIN

Navigation
3

I would like to return an object with your children, but only wanted to bring the children that corresponded to a certain condition. ex: I have the following classes: 

public class Fornecedor
{    
    public int FornecedorID { get; set; }
    public string CnpjCpf { get; set; }
    public string RazaoSocial { get; set; }
    public virtual ICollection<FornecedorContato> FornecedorContato { get; set; }
}

public class FornecedorContato
{
    public int FornecedorContatoID { get; set; }
    public int FornecedorID { get; set; }
    public bool Ativo { get; set; }
}

In case you would like within the FornecedorContato list of Fornecedor , return only elements that are Ativos How could I do this in a linq?

    
asked by anonymous 11.11.2016 / 17:29

3 answers

3

I found the following library that already abstracts this.

link

Using linq would look like this. 

var results = db
      .Fornecedor
      .IncludeFilter(f => f.FornecedorContato
                    .Where(fc => fc.Ativo == true))

I think it's a lot simpler this way.

    
11.11.2016 / 20:18
3

You will not be able to filter the items in a navigation property. The only way to apply a filter to the child records is by making the query separate.

Obviously the forms below consider you are using Lazy Loading. 

var fornecedores = db.Fornecedores.Select(f => new Fornecedor
                   {
                       Id = f.Id,
                       Nome = f.Nome,
                       //Outros campos
                       FornecedorContato = f.FornecedorContato.Where(c => c.Ativo)
                   });

Depending on the case, I would prefer to use an anonymous type, but I would need more details to know the best for your case. 

var fornecedores = db.Fornecedores.Select(f => new
                   {
                       Fornecedor = f,
                       Contatos = f.FornecedorContato.Where(c => c.Ativo)
                   });
    
11.11.2016 / 18:32
2

To filter an item of a relationship using Join you should write the direct filter in the relation ( Join or GroupJoin need IEnumerable<T> to work), in that case use GroupJoin : 

var results = db
      .Fornecedor
      .GroupJoin(db.FornecedorContato.Where(s => s.Ativo == true),
             f => f.FornecedorID,
             c => c.FornecedorID,
             (f, c) => new { f, p })
      .Select(e => new 
             {
               Fornecedor = e.f,
               FornecedorContato = e.p
             })
      .ToList();

this variable results should be accessed as follows: 

results[0].Fornecedor
results[0].Contatos

or 

foreach(var item in results)
{
   Client a = item.Fornecedor;
   IList<FornecedorContato> p = item.FornecedorContato.ToList();
}

This is simple access, but if you want to join you can make a ViewModel that represents an item with the information ( example 1 , example2 , example 3 ).

Note: If the Configuration.ProxyCreationEnabled = true; and db.Configuration.LazyLoadingEnabled = true; settings note that the list of objects will also have a list, but that list is not filtered the same shown in the example.

    
11.11.2016 / 20:02
Asp.Net MVC Inner Join statement return pre-defined values if the search does not produce results Call Jquery Validate on submit