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? — Tim Rogers
– Tim Rogers, Commented Nov 20, 2019 at 8:03

Tim Rogers · Accepted Answer · 2019-11-20 08:20:23Z

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, 2019 at 8:20

answered Nov 20, 2019 at 8:10

Tim Rogers

21.8k8 gold badges55 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Spook Over a year ago

This one seems to suit my needs best. Thanks!

Siavash Rostami · Accepted Answer · 2019-11-20 08:18:10Z

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, 2019 at 8:18

answered Nov 20, 2019 at 8:06

Siavash Rostami

1,9334 gold badges18 silver badges33 bronze badges

Comments

Pankaj · Accepted Answer · 2019-11-20 08:15:54Z

-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, 2019 at 8:15

answered Nov 20, 2019 at 8:10

Pankaj

2,7803 gold badges27 silver badges50 bronze badges

1 Comment

Tim Rogers Over a year ago

EF doesn't support filtered Includes.

Alexandru Ciprian Muresan · Accepted Answer · 2019-11-20 08:26:08Z

-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, 2019 at 8:26

answered Nov 20, 2019 at 8:08

Alexandru Ciprian Muresan

154 bronze badges

1 Comment

Tim Rogers Over a year ago

That gets Users that have any entry with a timestamp within the range, but includes all their entries.

Collectives™ on Stack Overflow

Extracting list of lists from Entity Framework

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related