C#•3w ago

Optimization problem for matching two lists of objects with same property

So I currently am running a database of ef core that needs to update userdata based on api calls and am faced with a speed problem So I have two lists: 1. List<Trader> (tracked by ef core) 2. List<User> that both have a property with the same value (trader id/user id) They both have a property "online" and I am attempting to set the list of traders "online" property to the same of the user The two lists are of random order but they have the same size and unique ids currently I am doing something like this (trader changes are auto tracked even in foreach loop):

foreach (Trader trader in traders)
{
    User user = users
        .First(user => user.UserId == trader.TraderId);
    trader.Online = user.Online;
}

foreach (Trader trader in traders)
{
    User user = users
        .First(user => user.UserId == trader.TraderId);
    trader.Online = user.Online;
}

would there be a linq or faster way to do the above?

6 Replies

PixxelKick•3w ago

It looks like you have a 1:1 relationship between trader and user which is a DB smell, you shouldn't have 1:1 relationships in a database (1:1 relationship means they should just be the same table) Unless it isn't 1:1, in which case you should have a navigation property on user to trader

PIE•3w ago

ah yes the user list I get is from an api wrapper call but I only need one part of the data (the online property) only the trader list is part of the db

PixxelKick•3w ago

Ohhh gptcha

PIE•3w ago

the user list I get is something like this

IEnumerable<Task<User>> users = traders
    .Select(trader => ApiWrapper.Get<User>(trader.TraderId));

IEnumerable<Task<User>> users = traders
    .Select(trader => ApiWrapper.Get<User>(trader.TraderId));

idk if this helps

PixxelKick•3w ago

var onlineGroups = users.GroupBy(u => u.Online);

foreach (var onlineGroup in onlineGroups)
{
    var ids = onlineGroup.Select(u => u.UserId).ToList();
    _ = await db.Traders
        .Where(t => ids.Contains(t.TraderId)
        .ExecuteUpdateAsync(e => e.SetPropety(t => t.Online, onlineGroup.Key);
}

var onlineGroups = users.GroupBy(u => u.Online);

foreach (var onlineGroup in onlineGroups)
{
    var ids = onlineGroup.Select(u => u.UserId).ToList();
    _ = await db.Traders
        .Where(t => ids.Contains(t.TraderId)
        .ExecuteUpdateAsync(e => e.SetPropety(t => t.Online, onlineGroup.Key);
}

I think I got that right, trying to do it off by heart That'll do it in effectively 2 update queries, one to set the offline group, one to set the online group Anytime you have a db query inside a loop, you can usually pull it out of the loop and use a .Contains to get em all in a single shot

PIE•3w ago

alr thanks will try it out o/

Gaming

Programming

Optimization problem for matching two lists of objects with same property