LINQ - filtering a result set and joining the result set

Question

I have User Table and each user can have multiple Computers... Lets say I am Ram, I can have only one DomainId, multiple UserId and multiple Computers..

I would like to get the DeviceId of all my computers and those should be only Laptops not Desktops

I am getting all the computers details of a given user

var res = (from b in DB.Users
          WHERE b.DomainId == 'MS\\aram'
select new {b.UserId, b.DeviceId}).ToList();

From the above deivces I want to get only those devices which are laptops... The device Id should be present in laptops table

My tblLaptops table is having a column called DeviceId.. I tried in the below query but it is throwing error cannot convert int into anonymous type

var devices = (from a in LBDB.tblLaptops where res.contains(a.DeviceId)
             select new {a.UserId, a.DeviceId }).ToList();

once the above is returned I also want to get Display Name of users which is in Extended Users table

Regarding the DisplayName, the column name is DisplayName and the table Name is ExtendedUser .. The ExtenderUserDevice table has DeviceId column which can be used to compare and the DisplayName of the device...

do you mind adding your data model and DbContext (with all private bits redacted out of course)? do you have any navigation properties defined on your entities? — timur, Mar 31 '20 at 12:08
at least use ``join`` between tables, like ``(from a in DB.tblLaptops join b in DB.Users on a.DeviceId equals b.DeviceId where b.DomainId == "MS\\aram" select new { a.UserId, a.DeviceId })`` — Mohammed Sajid, Mar 31 '20 at 12:14

Christos · Accepted Answer · 2020-03-31T17:45:45.597

You can try a join:

var result = (from laptop in DB.tblLaptops
              join user in DB.Users
              on user.DeviceId equals laptop.DeviceId
              where user.DomainId =='MS\\aram'
              select new { user.UserId, laptop.DeviceId }).ToList();

The above query would return the laptop devices and user ids for a specific domain. Regarding the DisplayName, we need some more info, in order to plug it also this in the above query, in order to fetch also this information.

Update

Since the above it is not going to work, since you access these tables through different contexts, here is my thought.

Provided that laptops is not a rather big table, you could fetch it in memory and make the join in memory. Apparently, this is not an optimal solution, but rather a workaround, that it wouldn't hurt you, if the laptops table is not big.

In terms of code:

// fetch the user devices in the specific domain:
var usersDevices = (from user in DB.Users
                   where user.DomainId == 'MS\\aram'
                   select new 
                   {
                       user.UserId, 
                       user.DeviceId
                   }).ToList(); 



// fetch **ALL** the laptops: 
var laptops = DB.tblLaptops.ToList();

// perform the join:
var userLaptops = (from laptop in laptops
                   join userDevice in usersDevices
                   on userDevice.DeviceId equals laptop.DeviceId
                   select new 
                   {
                       user.UserId, 
                       laptop.DeviceId 
                   }).ToList();

The correct approach it would be to think about, why these related info are behind different DbContext classes. That essentially means that these data are in different databases. If so, are these databases in the same machine ? If so and you don't plan these databases to be in different machines in the short future, you could quite probably makes all these queries in the database and fetch from the server that your application leaves only the needed data and not all the data and then filter/join them. IMHO just my 2 cents, based on many assumptions :)

This should work but unforyunately my tables tblLaptops and Users tables are in different context so join does not work here.. i have edited my post.. can you help on this — user3198688, Mar 31 '20 at 12:48

score 0 · Answer 2 · answered Mar 31 '20 at 12:18

given you tag your question with EF, you could potentially opt for .Include() on navigation properties but if we assume you just want the LINQ, here's a method chain version for your consideration:

var result = users
                    .Join(extendedUsers, u => u.UserId, eu => eu.UserId, (user, extUser) => new { user, extUser }) // you mentioned you want to join to ExtendedUser, so we might as well do it first. doesn't really make a difference
                    .Join(tblLaptops, u => u.user.DeviceId, l => l.DeviceId, (user, laptop) => new {user, laptop}) // then we get both results from previous join and add your laptop table onto it 
                    .Where(u => u.user.user.DomainId == "MS\\aram") // filter it all down
                    .Select(x => new {x.laptop.UserId, x.laptop.DeviceId, x.user.extUser.DisplayName}); // get the desired output

score 0 · Answer 3 · answered Mar 31 '20 at 12:19

I didn't have your databases to verify the code so I've created something quick:

enum SystemType
{
    Desktop = 0,
    Laptop,
}

struct User
{
    public int Id;
    public string Name;
}

struct SystemStruct
{
    public int Id;
    public SystemType Type;
}

private List<User> users = new List<User>() 
{ 
    new User() { Id = 0, Name = "John" }, 
    new User() { Id = 1, Name = "Alice" }, 
    new User() { Id = 2, Name = "Bob" } 
};

private List<SystemStruct> systems = new List<SystemStruct>() 
{ 
    new SystemStruct() { Id = 0, Type = SystemType.Desktop }, 
    new SystemStruct() { Id = 1, Type = SystemType.Laptop }, 
    new SystemStruct() { Id = 2, Type = SystemType.Desktop } 
};

It basically creates an enum and structs to map your database results.

And to join your users only where the system type is a laptop you use:

var laptopUsers = users.Join(systems.Where(s => s.Type == SystemType.Laptop), u => u.Id, s => s.Id, (user, system) => user);

LINQ - filtering a result set and joining the result set

3 Answers3