Left Join Entity Framework not running

Question

I'm trying to make a "LEFT JOIN".

However, only when I have information "in both tables" does the command return something.

Bellow my function

 public IList<ClientWorkFlowReadModel> GetWorkFlow(int idClient)
                {
                    try
                    {
                        using (GvisaContext db = new GvisaContext())
                        {
                            //Disable de proxy tracking for prevent error in json convertion
                            db.Configuration.ProxyCreationEnabled = false;

                            var clientServiceWorkFlows = db.ServicesWorkFlow
                               .Join(db.ClientsServicesWorkFlow, 
                                      swf => swf.IdServiceWorkFlow, 
                                      cswf => cswf.IdServiceWorkFlow, 
                                      (swf, cswf) =>
                               new { swf, cswf })
                               .Select(x => new ClientWorkFlowReadModel {
                                   Title = x.swf.Title,
                                   IdClient = x.cswf.IdClient,
                                   IdService = x.swf.IdService,
                                   IdClientServiceWorkFlow = x.cswf.IdClientServiceWorkFlow,
                                   Description = x.swf.Description,
                                   Active = x.swf.Active,
                                   DateUpdate = x.cswf.DateUpdate
                                  }
                               ).Where(y => y.IdClient == idClient).ToList();

                            return clientServiceWorkFlows;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

I need to do this:

SELECT * FROM dbo.Clients z
INNER JOIN dbo.ClientsServices y on y.idClient=z.idClient
INNER JOIN dbo.ServicesWorkFlow a on a.IdService=y.IdService
LEFT JOIN ClientsServicesWorkFlow b on b.IdClientServiceWorkFlow=a.IdServiceWorkFlow
WHERE z.IdClient=3

thanks!!!

When working with EF, [Don’t use Linq’s Join. Navigate!](https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/) — Ivan Stoev, Jan 23 '17 at 13:53

score 1 · Accepted Answer · edited May 23 '17 at 12:08

1

     var clientServiceWorkFlows = (from z in db.Clients 
     join y in db.ClientsServices on z.idClient equals y.idClient 
     join a in db.ServicesWorkFlow on y.IdService equals a.IdService
     join ClientsServicesWorkFlow b on a.IdServiceWorkFlow equals b.IdClientServiceWorkFlow into g 
     from x in g.DefaultIfEmpty()).Select([... what you want]
                               ).Where(y => y.IdClient == idClient).ToList();

Try the above. Let me know if it works.

Reference for Left joins in entity using DefaultIfEmpty, Entity framework left join

edited May 23 '17 at 12:08

Community

1
1

answered Jan 23 '17 at 13:16

Ya Wang

1,758
1
19
41

@AlexPereiraMahmud Which error? Did you import linq? – Ya Wang Jan 23 '17 at 15:49
I'm always getting the error "A query body must end with a select clause or a clause group" – Alex Pereira Mahmud Jan 23 '17 at 16:01

Left Join Entity Framework not running

1 Answers1