I have an intersection table in SQL Server that has 2 columns, UserId and ClientId. I also have a list validClientIds, that contains a list of client IDs.

I am trying to return a list of UserIds. The list should not contain any UserId that contains a ClientId that isn't in the list of validClientIds.

I have exhausted all of my brain trying to figure out this Entity Framework query, any help is much appreciated.

I am using .NET Framework 4.6

Example

Given the below data, I would like the returned list to contain UserId 2 and UserId 3, but not UserId 1 since UserId 1 has access to a client that is not in the validClientIds list.

CodePudding user response：

I simulated a query below and then use ANY to get only clients in the context

using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Xml;
using System.Xml.Linq;
using System.Data;

namespace ConsoleApp2
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            Context dbContext = new Context
            {
                clients = new List<Client>()
                {
                    new Client() { ClientId = 1 },
                    new Client() { ClientId = 2 }
                }
            };
            int[,] table = {{ 1,1}, { 1, 2 }, { 1, 3 }, { 2, 1 }, { 2, 2 }, { 3, 1 }, };

            var queryResults = Enumerable.Range(0, 6).Select(x =>
                new { user = new User() { UserId = table[x,0] }, client = new Client() { ClientId = table[x,1] } }

                ).ToList();

            var filteredResult = queryResults.Where(x => dbContext.clients.Any(y => y.ClientId == x.client.ClientId)).ToList();     
        }
    }
    public class Context
    {
        public List<User> users { get; set; }
        public List<Client> clients { get; set; }
    }
    public class User
    {
        public int UserId { get; set; }

    }
    public class Client
    {
        public int ClientId { get; set; }
    }
}

CodePudding user response：

It's should look like this

var validatedUsers = yourDbContext.users.Where(f => validClientIds.Contains(f.ClientId)); //Get's validated users
var unvalidatedUsers = yourDbContext.users.Where(f => !validClientIds.Contains(f.ClientId)); //Get's unvalidated users
var returnedUsers = validatedUsers.Where(f => !unvalidatedUsers.Any(k => k.UserId == f.UserId)); //delete unvalidated users from validated list

CodePudding user response：

jdweng's answer is generally fine but there are some options depending on what object you list is made of;

If you are using classes, then the following pattern is just fine ...

List<Test> list1 = new List<Test>() { 
    new Test() { Name = "Foo" },
    new Test() { Name = "Bar" },
    new Test() { Name = "OnlyInList1" },
};
    
List<Test> list2 = new List<Test>() { 
    new Test() { Name = "Foo" },
    new Test() { Name = "Bar" },
    new Test() { Name = "OnlyInList2" },
};

list1.Where(l1 => 
    list2.Any(l2 => l1.Property == l2.Property));

However, if you are using a primitive type, such as ints, then you may find Intersect() is the best option, as it's a little bit faster when using primitives.

List<int> list1 = new List<int>() { 1, 2, 3, 4, 5 };    
List<int> list2 = new List<int>() { 1, 2, 3, 4, 6 };

list1.Intersect(list2);

Why you wouldn't use Intersect on complex objects ...

The reason Intersect may not be the best option for classes would be that you must do a Select() on the comparison list to get the property values you wish to compare, like so ...

List<Test> list1 = new List<Test>() { 
    new Test() { Name = "Foo" },
    new Test() { Name = "Bar" },
    new Test() { Name = "OnlyInList1" },
};
    
List<Test> list2 = new List<Test>() { 
    new Test() { Name = "Foo" },
    new Test() { Name = "Bar" },
    new Test() { Name = "OnlyInList2" },
};

var list2Names = list2.Select(e => e.Name);
list1.IntersectBy(x, e => e.Name);

Personally, I like the syntax of IntersectBy but it's slower in comparison to the Where(e => f.Any()); on complex objects.

So, in summary, the options above cover must uses but in your case, the Where pattern jdweng mentioned is suitable.

.net Fiddle of the cases; Results:

// for 100,000 operations ...

// TestWithClass
// Intersect :        83ms
// Where.Any :        66ms

// TestWithPrimitive
// Intersect :        45ms
// Where.Any :        54ms