How to convert this SQL QUERY TO LINQ for Entity Framework

Question

I'm very new to LINQ, trying to learn right now the basics of querying, I need to convert this SQL to LINQ because of Entity Framework.

SELECT Desktop, Notebook,Gmail, Telefone, Smartphone, Rede, Outros 
FROM Atributos, Gestors, Funcionario, CentroCusto 
WHERE Funcionario.Id = Atributos.Funcionario_Id 
AND Funcionario.CentroCusto_Id = CentroCusto.Id 
AND CentroCusto.Gestor_Id = Gestors.Id 
AND Gestors.Email = 'john@email.com'

and what have you tried so far? What problem have you encountered? Please show your attempt. — ADyson, Apr 11 '19 at 13:52

score 3 · Answer 1 · answered Apr 11 '19 at 14:13

There is a pretty detailed explanation of this in another question here: How to do a join in linq to sql with method syntax?

However, I think this is what you're looking for:

from g in Gestors
    join f in Funcionario on g.Id equals f.Gestor_Id 
    join a in Atributos on f.Id equals a.Funcionario_Id
    join c in CentroCusto on f.CentroCusto_Id equals c.Id
where g.Email == "john@email.com"
select new 
{
    //your fields with their tables prefixes
}

For joins I like to use this syntax because it is easier to read and easier to convert from SQL for people who have just started with Linq. You can use the Lambda syntax, but if you are just starting out this will be the easiest to grasp until you have a little more practice with the Lambdas. Also, since I don't know what your table structure is like in the above query (indexes, fk's, pk's) you can probably improve performance significantly.

score 2 · Accepted Answer · answered Apr 11 '19 at 14:30

Entity creates classes that map to your database. So below is a sample of the classes and query to get data

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication108
{
    class Program
    {

        static void Main(string[] args)
        {
            string email = "john@email.com";
            DataBase db = new DataBase();
            var results = (from atr in db.Atributos
                           join fun in db.Funcionario on atr.Funcionario_Id equals fun.Id
                           join cen in db.CentroCusto on fun.CentroCusto_Id equals cen.Id
                           join ges in db.Gestors on cen.Gestor_Id equals ges.Id
                           where ges.Email == email
                           select new { desktop = atr.Desktop, notebook = atr.Notebook, gmail = atr.Gmail, tele = atr.Telefone, smartPhone = atr.Smartphone, rede = atr.Rede, outros = atr.Outros })
                           .ToList();
        }

    }
    public class DataBase
    {
        public List<Atributos> Atributos { get; set; }
        public List<Gestors> Gestors { get; set; }
        public List<Funcionario> Funcionario { get; set; }
        public List<CentroCusto> CentroCusto { get; set; }
    }
    public class Atributos
    {
        public string Funcionario_Id { get; set; }

        public string Desktop { get; set; }
        public string Notebook { get; set; }
        public string Gmail { get; set; }
        public string Telefone { get; set; }
        public string Smartphone { get; set; }
        public string Rede { get; set; }
        public string Outros { get; set; }
     }
    public class Gestors
    {
        public string Id { get; set; }
        public string Email { get; set; }
    }
    public class Funcionario
    {
        public string Id { get; set; }
        public string CentroCusto_Id { get; set; }
    }
    public class CentroCusto
    {
        public string Id { get; set; }
        public string Gestor_Id { get; set; }
    }






}

Worked for me! I think that the concept idea of SQL InnerJoin could've helped me to understand better how to do this query in LINQ, thanks — Pvxtotal, Apr 11 '19 at 14:47
When I try to build the view, I catch this Exception: The entity cannot be constructed in a LINQ to Entities query, do you have any idea how can I fix this? — Pvxtotal, Apr 11 '19 at 18:35

How to convert this SQL QUERY TO LINQ for Entity Framework

2 Answers2