Query with Many-to-Many Relationship Entity Framework

Question

Query with Many-to-Many Relationship Entity Framework

Navigation

#1 by (5 votes)

4

I'm new to Entity Framework and Linq, so my question may be very simple, but it's breaking my head.

I have two entities:

public class Fornecedor
    {
        public int FornecedorId { get; set; }
        public string Nome { get; set; }
        public virtual ICollection<Municipio> Municipios { get; set; }
    }


public class Municipio
    {
        public int MunicipioId { get; set; }
        public string Nome { get; set; }
        public virtual ICollection<Fornecedor> Fornecedores { get; set; }   
    }

I need to make an appointment to return all providers that serve a particular municipality. I will pass as parameter the name of the municipality, ex. São Paulo, and I need to receive a listing with all the suppliers that serve this municipality.

I hope I have been clear.

Thank you, Alexandre Previatti

c# entity-framework linq

asked by anonymous 23.12.2015 / 03:06

1 answer

How to extract only different values from an array? Problem when using Select

score 5 · Accepted Answer

In this case, it would be best to obtain the list of suppliers by the municipality, as follows:

var municipios= db.Municipio.Where(m => m.Nome == nome).ToList();

If the query returns only one municipality, or you want to return a municipality only, you can use .FirstOrDefault() , like this:

 var municipio= db.Municipio.FirstOrDefault(m => m.Nome == nome);

To access the suppliers simply access the list of suppliers of the municipality.

municipio.Fonecedores

You can use .Select() to get only the list too, like this:

var fornecedores= db.Municipio.FirstOrDefault(m => m.Nome == nome).Select(m => m.Fornecedores);

Remembering that db is your database context.

Editing

As noted by @JamesTK in the comments, it's worth pointing out some points:

If the Lazy Loading is disabled, you should add .Include() to your code. ex: var municipio= db.Municipio.Include(m => m.Fornecedores).FirstOrDefault(m => m.Nome == nome);
You could also search for employees, it would look something like this: db.Funcionarios.Where(f => f.Municipios.Any(m => m.Nome == "Sao Paulo"))