Extracting list of lists from Entity Framework

Question

Say, that I have the following entities defined:

public class User : IdentityUser
{
    public virtual string Name { get; set; }
    public virtual string Surname { get; set; }

    public virtual List<Entry> Entries { get; set; }
}

public class Entry
{
    [Key]
    public virtual int Id { get; set; }

    public virtual string UserId { get; set; }
    public virtual User User { get; set; }

    public virtual long Timestamp { get; set; }
    public virtual string Comment { get; set; }
}

I'd like to retrieve:

A list of all users
With a list of entries for each user (with empty list if the user has no entries)
With entries' Timestamp being between values X and Y

Obviously I can dump Users and Entries tables and organize data by myself, but that would be very inefficient. Is there a way to generate such a result with Entity Framework query?

Possible duplicate of [How to filter "Include" entities in entity framework?](https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework) — Tim Rogers, Nov 20 '19 at 08:03

Tim Rogers · Accepted Answer · 2019-11-20T08:20:23.603

3

You cannot filter an Include expression unfortunately, so you will have to create a projection - if there aren't that many properties, it's not too tedious.

   var users = from user in context.Users
     select new User { 
        Name = user.Name,
        Surname = user.Surname,
        Entries = user.Entries.Where(u => u.Timestamp > X && u.Timestamp < Y).ToList()
     }

edited Nov 20 '19 at 08:20

answered Nov 20 '19 at 08:10

Tim Rogers

21,297
6
52
68

This one seems to suit my needs best. Thanks! – Spook Nov 20 '19 at 08:22

Siavash Rostami · Answer 2 · 2019-11-20T08:18:10.290

0

Try this, it should work as far as i see in your question:

var users = context.Users
    // exclue this where caluse if you want the user to be loaded anyway
    .Where(c => c.Entries.Any(i => i.Timestamp >= X && i.Timestamp <= Y))
    .Select(c => new
    {
        User = c,
        Entries = c.Entries.Where(i => i.Timestamp >= X && i.Timestamp <= Y))
    })
    .FirstOrDefault();

edited Nov 20 '19 at 08:18

answered Nov 20 '19 at 08:06

Siavash Rostami

1,883
4
17
31

Alexandru Ciprian Muresan · Answer 3 · 2019-11-20T08:26:08.773

-1

You can achieve this by using your database context. I assuming that your database context is using eager loading so you can do something like this:

_yourDataBaseContext.Set<User>()
.Select(it=> new User
{
     Name = it.Name,
     Surname = it.Surname,
     Entries = it.Entries.ToList().Where(e=> e.Timestamp > x && e.Timestamp <y)
})
.Include("Entries")
.ToList();

edited Nov 20 '19 at 08:26

answered Nov 20 '19 at 08:08

Alexandru Ciprian Muresan

15
4

That gets Users that have _any_ entry with a timestamp within the range, but includes _all_ their entries. – Tim Rogers Nov 20 '19 at 08:11

Pankaj · Answer 4 · 2019-11-20T08:15:54.510

-1

A list of all users

_dbContext.Users();

With list of entries for each user (with empty list if user has no entries)

 using System.Data.Entity;

_dbContext.Users().Include(x => x.Entries);

With entries' Timestamp being between values X and Y

using System.Data.Entity;

_dbContext.Users().Where(x => x.Entries.Any(y => y.Timestamp >= X && i.Timestamp <= Y))
.Select(c => new
{
    User = c,
    Entries = c.Entries.Where(x => x.Timestamp >= X && x.Timestamp <= Y))
}).ToList();

edited Nov 20 '19 at 08:15

answered Nov 20 '19 at 08:10

Pankaj

2,618
3
25
47

EF doesn't support filtered Includes. – Tim Rogers Nov 20 '19 at 08:14

Extracting list of lists from Entity Framework

4 Answers4