Struggling to get this in Linq

[BitLost]

I have a SQL statement that I have checked for function, as this was driving me nuts:

SELECT Site.*, Job.*
FROM Site INNER JOIN Job ON Site.SiteID = Job.SiteID
ORDER BY BookingDate;

I am trying to create this in Linq(have I got that right?). Something like:

[COLOR=blue]var[/COLOR] siteHistory = [COLOR=blue]from[/COLOR] s [COLOR=blue]in[/COLOR] _context.Sites
          .Include(w => w.Job)
          .OrderBy(w=>w.Job.BookingDate)
          [COLOR=blue]select[/COLOR] s;

But when I create this, Intellisense cannot find BookingDate within Job and gives me nice red squiggles. Nice if I am wanting to make red squiggles but doesn't help me with what I am trying to do.

Why wont this work?

What is the correct statement?

 

[jmcilhinney]

What is the type of that Job property? I'll wager that it's not Job, but rather ICollection<Job> or the like.

 

[BitLost]

What is the type of that Job property? I'll wager that it's not Job, but rather ICollection<Job> or the like.

Yes it is. Does this mean I cant do what I want to do?

 

[jmcilhinney]

The issue is that you're dealing with a one-to-many relationship so you can't simply order the Sites by the BookingDate of a related Job because, in theory at least, there may be multiple associated Jobs. Can there be more than one associated Job in practice? Can there be no associated Job?

By the way, you should configure your context to use plural names for properties that navigate one-to-many relationships. That way, the name would have been Jobs rather than Job and the issue would have been more obvious.

 

[BitLost]

The issue is that you're dealing with a one-to-many relationship so you can't simply order the Sites by the BookingDate of a related Job because, in theory at least, there may be multiple associated Jobs. Can there be more than one associated Job in practice? Can there be no associated Job?

By the way, you should configure your context to use plural names for properties that navigate one-to-many relationships. That way, the name would have been Jobs rather than Job and the issue would have been more obvious.

Not what I was trying or intending to do.

What I am doing is I have sites with multiple jobs.

I want to list the jobs by booking date order. Which is exactly what the SQL statement does. So when I view them in a view as a related item they are only showing the more recent jobs by this site.

I cant seem to find the appropriate method to do this.

 

[jmcilhinney]

If the query is supposed to get Jobs then why is it getting Sites? You should be querying the Jobs and including the Sites, not the other way around.

 

[BitLost]

If the query is supposed to get Jobs then why is it getting Sites? You should be querying the Jobs and including the Sites, not the other way around.

I thought this way too. This will work for this one instance. However not in all instances where I need to do this.

Take the below, which also needs to display a list of jobs in order as a part of the view. I am not sure it would work starting from the job table.

[COLOR=blue]var[/COLOR] site = [COLOR=blue]await[/COLOR] _context.Sites
               .Include(w => w.Client)
               .Include(w => w.SiteType)
               .Include(w => w.WaterBodys)
               .Include(w => w.Job)
                   .AsNoTracking()
               .SingleOrDefaultAsync(m => m.SiteID == id);

And this is only one specific instance. I can think of dozens where I need to order by a field within the collection.

I will try reversing it and see if I can get it to work. Just wondering out loud.

 

[BitLost]

I have half figured it out...I need to make two separate queries. That's fine as far as I have got it. But how to join them together?

 

[jmcilhinney]

You seem to be under the impression that you should be able to retrieve data in one specific way and have that work regardless of what you're trying to achieve. Disabuse yourself of that notion now. You need to write the appropriate code for every specific situation. If you need to display something different in two specific cases then you will likely need to write two different queries. That's it, that's all. There is no "join them together". You write the code you need to do what need to do.

I can think of dozens where I need to order by a field within the collection.

Well then there's something wrong with your thinking because that makes no sense. Let's say that you have a Parent entity and a Child entity and Child has a Number property. One Parent has two related Child entities with Number values 1 and 3 while a another has one related Child with a Number value of 2. To say that you are going to sort those Parent entities by the Number of the related Child entities has no meaning because the first Parent would have to come both before and after the second. It is meaningful to say that you will sort them by the Number of their first Child or their last Child but that is something different to what you said.

 

[jmcilhinney]

Take the below, which also needs to display a list of jobs in order as a part of the view. I am not sure it would work starting from the job table.

[COLOR=blue]var[/COLOR] site = [COLOR=blue]await[/COLOR] _context.Sites
               .Include(w => w.Client)
               .Include(w => w.SiteType)
               .Include(w => w.WaterBodys)
               .Include(w => w.Job)
                   .AsNoTracking()
               .SingleOrDefaultAsync(m => m.SiteID == id);

Firstly, why are you using both 'w' and 'm' to refer to a Site when 's' is the obvious choice? That's a great way to cause confusion.

As for the issue, I repeat, if your aim is to display Job entities then you should be querying Jobs, not Sites. If what you want is to list all the Jobs for a specific Site along with all that other related data then the query would, from what I can gather, look like this:

var jobs = await _context.Jobs
                         .Include(j => j.Site.Client)
                         .Include(j => j.Site.SiteType)
                         .Include(j => j.Site.WaterBodys)
                         .Where(j => j.Site.SiteID == id)
                         .AsNoTracking()
                         .ToListAsync();

 

[BitLost]

That's what I think I was asking.

Its hard with this to get your brain going the right direction.

The use I see for this is multiple is because of a situation like wanting to look at the job and see all related time card entries. Or look at the site and see all the related jobs regardless of water body or...and lots and lots and lots in a practical sense. This is what I am meaning

 

[jmcilhinney]

When you use a SQL join, you end up with a result set where each row contains data from both tables. That doesn't happen in EF, where you're dealing with discrete entities. Your original SQL query will produce a result set with columns from both tables with one row for each record in the Job table and potentially multiple versions of the same data from the Site table. With EF, you can approach the query from two different directions. It can be parent-centric or child centric. If you do this:

context.Parents.Include(p => p.Children)

then you will get a collection of Parent entities where each Parent has a collection of related Child entities. If you do this:

context.Children.Include(c => c.Parent)

then you will get a collection of Child entities where each Child has a single related Parent entity. The same Parent object may be referred to by multiple Child objects.

Which of these is the more appropriate depends on the situation. Do you want to display a single Parent entity to the user? Then the first option is the one to go for.

The thing is, you can only use the query itself to sort the root entity. That means that the first option can only sort Parent entities, not Child entities. If you want to sort Child entities using the query then you must use the second option.

So, what if you have competing requirements, i.e. display one Parent and sort the Child entities? The solution is to do the sorting after the query. You can use the first query to retrieve your data and then you can sort the Child entities in the controller or the view. In that case, you'll be using LINQ to Objects rather than LINQ to Entities. It's still the same OrderBy method but the underlying LINQ provider is different.