CODEWITHSUNDEEP
c#entity-framework
Spook
Spook

Reputation: 25927

Extracting list of lists from Entity Framework

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:

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?

Upvotes: 0

Views: 1317

Answers (4)

Alexandru Ciprian Muresan
Alexandru Ciprian Muresan

Reputation: 15

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();

Upvotes: -1

Tim Rogers
Tim Rogers

Reputation: 21713

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()
     }

Upvotes: 3

Siavash Rostami
Siavash Rostami

Reputation: 1933

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();

Upvotes: 0

Pankaj
Pankaj

Reputation: 2744

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();

Upvotes: -1

Related Questions