Filtering data on related objects

Question

I'm having an issue with retreiving the correct data set from my database. And my knowledge about it is not enough to get it fixed.

I’m creating an application for my uncle his transport company. So he has drivers who drive an assignment and for each assignment the driver drives multiple rides. So it looks like: One driver has zero or multiple assignments but an assignment is for one driver only and an assignment has multiple drives but a drive is for one assignment only.

What I’m trying to do is to get all drives of a driver from a month month because I need to export this to an Excel and I want to create a monthly overview in the app. This is what I did to get all the drives from a driver all time:

public Chauffeur FindAlleOpdrachtenRitten(string id)
{
    return _ctx.Users
    .Include(o => o.Opdrachten)
    .Include(o => o.Opdrachten.Select(x => x.Ritten))
     // .Where(o => o.Opdrachten.(r => r.Datum == DateTime.Now))
    .SingleOrDefault(x => (x.Id == id));
}

Or to put it in a (my)SQL statement it should look like this:

    SELECT * FROM ICS.AspNetUsers as a 
    INNER JOIN ICS.Opdrachts as o on a.Id = o.ChauffeurID 
    INNER JOIN ICS.Rits as r on o.OpdrachtID = r.OpdrachtID
    WHERE a.UserName = "dimitrivantillo@gmail.com"
    AND r.Datum > "1-12-2016";

The code snippet allready shows something I've allready tried and does not work (and the DateTime.Now was just to try something). So I'm looking for some guidance on how to solve this. Am I looking in the correct direction? I found some other topics with more or less the same problem but not close enough to my situation.

The export to excel function is working but my ultimate goal is to save the database by not querying for all the table rows and only query for a specific month. I’ve been thinking of just getting all the rows and filter the specific month afterwards but wouldn’t that consume a lot of resources in the end? (after a few years).

Also note that I'm Dutch speaking. Chauffeur = drivers, Opdracht = Assignment and Rit = drive

Thanks for reading and hopefully helping me:D

Edit: the includes themselves are working, I get all the necessary data but not the filtered related object. So all drives instead of the drives for a specific month because the where function does not work

You can use DateTime comparison operators, they will be translated to SQL. So `.Where(o => o.Datum >= new DateTime(2016, 12, 01)`? — CodeCaster, Dec 30 '16 at 22:59
Mhmm I might have explained it unclear but the datum property is part of the drive (Rit). So chauffeur.opdrachten.ritten.date. Chauffeur is The driver having multiple assignments and rit is a drive. So chauffeur has properties like first and last name, assignment has properties like from and where to and the drive/rit has a time stamp, how many miles driven and so — Dimitri Van Tillo, Dec 30 '16 at 23:14

score 0 · Accepted Answer · edited May 23 '17 at 12:33

Include can't be filtered. However, applying what I explained here, but one level deeper, you can achieve what you want by:

_ctx.Configuration.LazyLoadingEnabled = false;
var user = _ctx.Users
               .Where(u => u.Id == id)
               .Select(u => new
               {
                   User = u,
                   Opdrachten = u.Opdrachten,
                   Ritten = u.Opdrachten
                             .SelectMany(o => o.Ritten
                                               .Where(r =>  r.Datum == DateTime.Now))
               })
               .SingleOrDefault();
return user?.User;

The query collects all data you need into the context's cache and EF connects them to one another by relationship fixup. So each Opdracht will have a Ritten collection that's populates by today's items only.

Lazy loading is disabled, otherwise EF will still populate the navigation properties from the database when you access them.

I feel stupid for not seeing that you allready gave the solution. I did read the link you posted but I thought it was not applicable to my situation. Many thanks and I marked it as the correct answer! (there is a ")" missing behind .Where(u => (u.Id == id))<== ) — Dimitri Van Tillo, Dec 31 '16 at 16:48

Filtering data on related objects

1 Answers1