Structure this into one statement?

Question

I have a database, that includes a one to many foreign key, eg

Table {shop
[Id]
...}

Table {user
[Id]
[Shop_id]
[Archived]
}

I also have a method as below

public IEnumerable<shop> GetShopDetails(int shopId)
{
    var foo = (from s in context.Shops
    where s.Id = shopId
    select s).ToList();

    return foo;
}

As a consequence of this, it will return all users for that shop. Most of the time I only really want the users that are Not Archived.

Is there a way of writing this into the one statement, so I can pass in, say a second parameter of includeArchived, and use that to determine if I return all users, or just those that are active.

At present, I can make it work either by addind a method to my shop object that returns a subset of users, or I can load the shop, get its id, and then create a seperate collection of users that contain the appropriate fk, but either method seems a little clunky to me.

You'll need to do a join: http://stackoverflow.com/questions/3217669/how-to-do-a-join-in-linq-to-sql-with-method-syntax — Nick, Mar 19 '13 at 14:20
Why are you converting the query to a list? Why not just return the `IQueryable` and allow the caller of the method to filter it further (at the database level) if they wish? — Servy, Mar 19 '13 at 14:21
@Servy - the dangers of learning EF from the internet Im afraid, Im picking up bad habits from bad tutorials. — Matt, Mar 19 '13 at 14:25
@Matt `ToList` is not your friend when working with any query provider. It's not something that should be used much, and is a last resort when it's the only way to get the needed functionality. (Barring debugging/development, in which it's quite useful.) — Servy, Mar 19 '13 at 14:26

score 2 · Accepted Answer · answered Mar 19 '13 at 14:22

2

Just conditionally add another Where condition:

public IQueryable<user> GetShopUsers(int shopId, bool includeArchived = false)
{
    var foo = from u in context.Users
              where u.Shop_id = shopId
              select u;

    if(!includeArchived)
        foo = foo.Where(u => !u.Archived);  

    return foo;
}

answered Mar 19 '13 at 14:22

lc.

113,939
20
158
187

I may be breaking EF rules or best practice, but I need to display some info from the shop as well, hence why I am returning the shop, which contains the filtered list of users, as opposed to just the list of users. The 2 tables are already joined on a fk, but Im guessing that I need some kind of explicit join or include statement to do what I need. best I have so far returns me N copies of the shop (tallying to active users), each containg all users. – Matt Mar 19 '13 at 14:30
In that case you could take a look at [this trick](http://stackoverflow.com/questions/12996617/filtering-navigation-property-in-eager-loading), look at @Botz3000's second suggestion, or just do one query for the shop and another for the non-archived users. – lc. Mar 19 '13 at 14:40

Botz3000 · Answer 2 · 2013-03-19T14:37:43.220

0

I guess this is what you want? You can integrate another boolean parameter into your query just as you already did with shopId.

public IQueryable<user> GetUsers(int shopId, bool includeArchived)
{
    return from user in context.Users
           where user.Shop_id = shopId
           where includeArchived || !user.Archived
           select user;
}

UPDATE: Not sure if you can filter the Shop Entity's own User collection. You could try a query constructing an anonymous object:

var foo = from s in context.Shops
          where s.Id = shopId
          select new {
              Shop = s,
              Users = s.Users.Where(u => includeArchived || !u.Archived)
          };

edited Mar 19 '13 at 14:37

answered Mar 19 '13 at 14:22

Botz3000

39,020
8
103
127

@Matt You could try constructing an anomymous type in your query. See my update. Not sure if it works though. – Botz3000 Mar 19 '13 at 14:41
Thanks, I looked at that, and I can get foo to be an anonymous type (if I expand it it returns a tuple of shops and users.) which I cannot then convert back to a dealership, nor can I drill into the users collection as the reader is already open. I guess that Im trying to do somethign that can't be done. (Or at least I can't figure it yet.) I'll carry on doing it the clunky way for now I guess, thanks thoguh – Matt Mar 19 '13 at 14:51

Structure this into one statement?

2 Answers2