Query a many-to-many table of tables in EDMX
I am currently facing a performance issue when I access the navigation property of an object.
I have three tables: UserCategory
, User
and UserCategoryUser
. Between tables User
and UserCategory
there are many, many . UserCategoryUser
table is a table of tables, and it has two columns, UserId and UserCategoryId, and the primary keys of the tables User
and UserCategory
. The sink table is UserCategoryUser
used to maintain a many-to-many relationship between tables User
and UserCategory
.
I am using the first database approach. Hence, in my EDMX for an object User
, I have one navigation property UserCategories
. Similarly, UserCategory
there is a navigation property for an object Users
.
I want to add a user to a custom category. So before adding I do a check if the user is added to the custom category or not. In my database I have about 100 thousand. User records and only one user category. All users associated with a single custom category.
I am accessing the nav property Users
like:
var userCategory = Context.UserCategories.FirstOrDefault(uc => uc.UserCategoryId == userCategoryId);
if (userCategory != null)
{
if (userCategory.Users.Any(u => u.Username == username))
{
//Other operations
}
}
This code worked before, but now it hangs as we have a lot of user data. In particular, it hangs on the line userCategory.Users.Any(u => u.Username == username))
. I also tried to get users count
, but still it hangs!
userCategory.Users.Count();
I cannot execute a join query in LINQ to Entity because the joiner ( UserCategoryUser
) table is NOT being added as an entity in EDMX.
How to solve this problem? I can test this using raw SQL or a stored procedure, but I want to stay away from those for this case.
source to share
Your problem is that with lazy loading enabled, the call userCategory.Users
loads all users into memory. I think, given that there are very few categories, I would do it the other way around. that is, get the user, then check if the custom category collection contains the added category.
However, if you had a very large number of categories, you can explicitly filter the collection. Disable lazy loading on collections first (by removing the keyword for example virtual
), then try this:
Context.Entry(userCategory)
.Collection(uc => uc.Users)
.Query()
.Where(u => u.Username == username)//I'd put an index on username too
.Load();
if(userCategory.Users.Any())
{
//Other operations
}
Link: https://msdn.microsoft.com/en-us/data/jj574232.aspx#explicitFilter
source to share