CODEWITHSUNDEEP
c#linq
iOSdeveloper
iOSdeveloper

Reputation: 113

Complex query with if

I need to make a query like getUsers (list IDs); I need to search by ids, if found then return users with ids in list, if not found then return all users, or if found only one return one user.

How can I write this query?

This is how I started:

 public List<User> getUsers (List<int> ids)        
 {
    using(var uow = _repository.CreateUnitOfWork())
    {               
        var u = uow.GetEntities<User>().Where(c => c.Id in ids);
        if (u == null) 
        u.ToList();                     
    }
}

Upvotes: 0

Views: 87

Answers (5)

iOSdeveloper
iOSdeveloper

Reputation: 113

public IList<User> getUsers (List<int > ids = null)
{                       
        var query = _repository.GetEntities<User>();

        if (ids == null)                
            return query.ToList();                  

        if (ids.Count()==1)
        {                    
             var singleUser = query.FirstOrDefault(user => ids.Contains(user.Id));
             if (singleUser!= null)                                     
                return new List<User>{ singleUser; };           

             return new List<User>();                
        }   

        return query.Where(user => ids.Contains(user.Id)).ToList();                                                     
}

Upvotes: 0

Lucian Bumb
Lucian Bumb

Reputation: 2881

a simple way is to use Join

public List<User> getUsers (List<int> ids)        
 {
    using(var uow = _repository.CreateUnitOfWork())
    {               
        var u = uow.GetEntities<User>()
                      .Join(ids,x=>x.Id,y=>y,(x,y)=>x).ToList();
        if (u.Count==0) 
               return uow.GetEntities<User>().ToList()
         return u;           
    }
}

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205629

Assuming uow.GetEntities<User>() returns IQueryable<User>, when there are users with ids from the list, the proposed solutions involve executing two expensive IN (...) SQL queries, also building and passing the ids list twice to the database - one for Any and one for Where.

I would rather structure it differently. I would execute a query with Where based on ids.Contains and materialize the result in memory. Then I would check locally if it contains data, and if yes, will return the result, otherwice will execute a second query without filter, which should be much more efficient.

Something like this:

public List<User> getUsers (List<int> ids)        
{
    using(var uow = _repository.CreateUnitOfWork())
    {               
        var allUsers = uow.GetEntities<User>();
        var matchingUsers = allUsers
            .Where(user => ids.Contains(user.Id))
            .ToList();
        return matchingUsers.Any() ? matchingUsers : allUsers.ToList();
    }
}

To recap, you cannot do what you want with a single database query. It requires executing at least two queries and the only question is to use the most efficient approach.

Upvotes: 2

sdgfsdh
sdgfsdh

Reputation: 37065

Your question doesn't make sense because the return type of a function must always be the same. It cannot return a List some of the time and a User the rest of the time.

I would suggest something like this: 

public IEnumerable<User> GetUsersByIdOrAllUsers(IEnumerable<int> ids)
{
    using (var uow = _repository.CreateUnitOfWork())
    {
        var users = uow.GetEntities<User>();

        if (users.Any(c => ids.Contains(c.ID)))
        {
            return users.Where(c => ids.Contains(c.ID));
        }

        return users;
    }
}

You can then test if you only found one user:

var matchingUsers = GetUsersByIdOrAllUsers(ids);

if (matchingUsers.Any() && !matchingUsers.Skip(1).Any())
{
    var singleUser = matchingUsers.Single();

    // Do stuff with the single user...
}

Note that the use of IEnumerable makes operations lazy, and therefore more efficient. If you really want a List, just do: 

var matchingUsersList = matchingUsers.ToList()

Upvotes: 2

David
David

Reputation: 218877

Not sure if there's a less chatty way to do it, but logically maybe something like this?:

if (uow.GetEntities<User>().Any(u => ids.Contains(u.ID))
    return uow.GetEntities<User>().Where(u => ids.Contains(u.ID)).ToList();
return uow.GetEntities<User>().ToList();

I'm assuming here that uow.GetEntities<User>() simply returns a queryable and doesn't itself materialize anything from the database or have any significant performance penalty. If that's not the case, the code for this operation may need to be placed deeper in the DAL.

Upvotes: 0

Related Questions