Linq filttar elements of LEFT JOIN

Question

I would like to return an object with your children, but I just wanted you 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 I would like that within the list FornecedorContato of Fornecedor, return only the elements that are Ativos How could I do that in a single?

Answer 1

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

Obviously the forms below consider that 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’d prefer to use an anonymous guy, but I’d need more details to know what’s best for your case.

var fornecedores = db.Fornecedores.Select(f => new
                   {
                       Fornecedor = f,
                       Contatos = f.FornecedorContato.Where(c => c.Ativo)
                   });

Answer 2

I found the following library that already abstracted it.

http://entityframework-plus.net/

Using it the Bible would look like this.

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

I believe that this way becomes much simpler.

Answer 3

To filter an item from a relation using Join must write the filter directly in the interface (Join or GroupJoin need a IEnumerable<T> to function), in that specific 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();
}

These are simple accesses, but if you want to join can be made a ViewModel representing an item with the information (example 1, exemplo2, example 3).

_{Observing: if the settings Configuration.ProxyCreationEnabled = true; and db.Configuration.LazyLoadingEnabled = true; note that in the relation of objects they will also have a list, but this is not filtered as shown in the example.}