0

Today I ran into a problem with Entity Framework. I'm not sure if this is a weird bug or that i'm doing something wrong. I've already looked all over the forum for any possible solutions, but none I found worked for me.

I have the following LINQ query:

return (from sp in context.ServiceProviders.DefaultIfEmpty()
    join pl in context.Platforms on sp.Id equals pl.ServiceProviderId into innerPl
    from pl in innerPl.DefaultIfEmpty()
    join pp in context.Participants on pl.Id equals pp.PlatformId into innerPp
    from pp in innerPp.DefaultIfEmpty()
    join ps in context.Paymentsettlements on pp.Id equals ps.ParticipantId into innerPs
    from ps in innerPs.Where(ps => ps.ConfirmedOn.HasValue && ps.ExportDate.HasValue && !ps.StatisticsDate.HasValue).DefaultIfEmpty()
    select sp).Include(sp => sp.Environment)
                .Include(sp => sp.Platforms.Select(pl => pl.Participants.Select(pp => pp.Paymentsettlements.Select(ps => ps.Requester))))
                .Include(sp => sp.Platforms.Select(pl => pl.Participants.Select(pp => pp.Paymentsettlements.Select(ps => ps.Payer))))
                .ToList();

The result i'm looking for is that i always get the ServiceProvider no matter if there are objects inside the ServiceProvider. I am getting this result at the moment, but the where I've put in the query does not get taken into account. The following where does not make any difference:

innerPs.Where(ps => ps.ConfirmedOn.HasValue && ps.ExportDate.HasValue && !ps.StatisticsDate.HasValue).DefaultIfEmpty()

If the StatisticsDate has a value, those Paymentsettlements also are given in the output.

I've already tried to put the WHERE statement on the context.Paymentsettlements object.

I hope anyone can help me with this problem.

Kind regards,

Rob H

RobH
  • 1
  • 1
  • 1
    Did you need `LEFT OUTER JOIN`? Because, what you have done is left join. – Farhad Jabiyev Feb 23 '15 at 16:10
  • Yes, I always want all objects (it only has to filter out the Paymentsettlements that do not meet the WHERE-clause requirements, but if there are no Paymentsettlements I do want the objects above) – RobH Feb 23 '15 at 16:12
  • Not sure, but maybe it is because the lazy execution. Try adding .ToList() after the where statement. (ie - innerPs.Where(ps => ps.ConfirmedOn.HasValue && ps.ExportDate.HasValue && !ps.StatisticsDate.HasValue).ToList().DefaultIfEmpty() – Ziv Weissman Feb 23 '15 at 16:16
  • Thanks Ziv, I've tried. Still getting the same result (Paymentsettlement objects with StatisticsDate filled). – RobH Feb 23 '15 at 16:20
  • 1
    `from ps in innerPs.Where(ps => ps.`, is this compiled? Can you try to rename `ps => ps` to something else? – Giorgi Nakeuri Feb 23 '15 at 16:20
  • Thanks Giorgi, it does compile. I've changed it to ps2 as you suggested, still the same result. – RobH Feb 23 '15 at 16:22
  • @GiorgiNakeuri While what he did is probably poor style, it is not at all incorrect: [example of similar usage](http://blogs.msdn.com/b/ericlippert/archive/2009/11/02/simple-names-are-not-so-simple.aspx) [part two](http://blogs.msdn.com/b/ericlippert/archive/2009/11/05/simple-names-are-not-so-simple-part-two.aspx) – Servy Feb 23 '15 at 17:09

2 Answers2

0

Actually you are doing left join and then selecting ServiceProviders. Here you are getting all providers. Then you are including all child elements: select sp).Include(sp => sp.Environment). This won't work. It will include all rows.

What you can really do is select to anonymous type like

select new {sp, ps }

Unfortunately there is no way of filtering in included objects. Include is something like all or nothing. You can read about it:

How to filter nested collection Entity Framework objects?

EF Query With Conditional Include

Community
  • 1
  • 1
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • Thanks for the answer. I already had it working with anonymous objects, but i'd thought there had to be a way to use my models as they are intended. – RobH Feb 23 '15 at 17:58
0

I've finally made another (hacky) solution. Here is my final code:

using (var context = new BetaalplatformContext())
        {
            var dienstverleners = context.Dienstverleners.Include(dv => dv.Omgeving)
                                                         .Include(dv => dv.Platformen)
                                                         .Include(dv => dv.Platformen.Select(pl => pl.Deelnemers))
                                                         .Include(dv => dv.Platformen.Select(pl => pl.Deelnemers.Select(dn => dn.Betaalregelingen)))
                                                         .Include(dv => dv.Platformen.Select(pl => pl.Deelnemers.Select(dn => dn.Betaalregelingen.Select(br => br.Aanvrager))))
                                                         .Include(dv => dv.Platformen.Select(pl => pl.Deelnemers.Select(dn => dn.Betaalregelingen.Select(br => br.Betaler))))
                                                         .ToList();

            dienstverleners.ForEach(
                dv => dv.Platformen.ForEach(
                    pl => pl.Deelnemers.ForEach(
                        dn => dn.Betaalregelingen = dn.Betaalregelingen
                            .Where(br2 => br2.BevestigdOp.HasValue && br2.ExportDatum.HasValue && !br2.StatistiekDatum.HasValue)
                            .ToList()
                        )
                    )
                );

            return dienstverleners;
        }

This way i am abled to keep my models intact (I don't like to use anonymous objects).

RobH
  • 1
  • 1