LINQ - filtering a result set and joining the result set

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...

3 answers

  • answered 2020-03-31 12:10 Christos

    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 :)

  • answered 2020-03-31 12:18 timur

    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
    

  • answered 2020-03-31 12:19 Mike

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