What is the way to join these two list?

Question

I have a IList<User> that contains objects with a pair of value: Name and Surname. On the database I have a table that contains rows with Name and Surname field. I want on codebehind to return the list of the rows that match my List, so let say have Name and Surname (respectively) equals.

My actual code is:

utenti = (from User utente in db.User.AsEnumerable()
          join amico in amiciParsed 
               on new { utente.Nome, utente.Cognome } equals 
                  new { Nome = amico.first_name, Cognome = amico.last_name }
          select utente).OrderBy(p => p.Nome)
                        .OrderBy(p => p.Cognome)
                        .OrderBy(p => p.Nickname)
                        .ToList();

but this it is not good for two reasons:

It will download the whole records of the DB on the client;
I can't match Name and Surname as case sensitive (example Marco cordi != Marco Cordi); and on DB I have every kind of up/down chars.

As suggested on a previously question, seems that this answer can't help me, since I have to do a join (and also because the first problem it is not related).

What's the way to resolve this problem?

Large? :O Could be over 2000 records, but also 10 :) It depends. I would say an avarage of 600 records. — markzzz, Nov 06 '13 at 11:20
OrderBy doesn't work as you expect, it will just sort your collection 3 times, look at ThenBy — Andrey, Nov 06 '13 at 11:29
@Andrey: thank you for the tip. Still I have the others two problems :P — markzzz, Nov 06 '13 at 11:31
@markzzz well if that list doesn't get too large, it would be faster sending it to the database and letting it do the query, instead of retrieving the whole database to do the comparison in code. Consider using a stored procedure and send your list to the stored procedure instead, user a table typed parameter. — David S., Nov 06 '13 at 11:32
@DavidS.: For some reasons I prefeer keep the query on codebehind. I can download the records on client, ok! But still I can't do any ToLower operation :( — markzzz, Nov 06 '13 at 11:34
Agreed in this case it's easier for the database to the comparison in the form of a stored proc. What you get back from it is cleaner. — betelgeuce, Nov 06 '13 at 11:34
@markzzz if you don't want to download the database as in point 1 of your requirements you need to do the computation on the database site of things. Otherwise you are probably going to have to pull over the dataset and do the comparison there. Intersect function be any use to you? — betelgeuce, Nov 06 '13 at 11:36
If this is the only way, ok I'll pull down the records. But the ToLower it is not usable...! Not sure about what do you mean with Intersect function here :O — markzzz, Nov 06 '13 at 11:39
No pull down all records is not the only way to do it. You could do it in the database but for some reasons you prefer to keep the query in code behind. Why do you prefer to not have the database do what a database does? If you do download are you stuck with LINQ on the client? — paparazzo, Nov 06 '13 at 12:59

Ovidiu · Answer 1 · 2013-11-06T11:56:30.867

I don't know if this will work in your situation, but you might give it a try.

First, create a new list of strings:

List<string> amici = aimiciParsed.Select(x => x.first_name + "|" + x.last_name).ToList();

Then, select the users from DB, based on this list

var utenti = db.User.AsEnumerable().Where(utente => 
    amici.Contains(utente.Nome + "|" + utente.Cognome)).ToList();

It sends the list of strings to the DB as a list of parameters and translates it into a query like

SELECT * FROM User WHERE User.Nome + "|" + User.Cognome IN (@p1, @p2, @p3 ...)

Unfortunately, there is no way to call Contains with something like StringComparison.OrdinalIgnoreCase, so you might have to change the collation of your columns.

score 0 · Answer 2 · edited May 23 '17 at 12:34

This could be done with PredicateBuilder:

using LinqKit;

var predicate = PredicateBuilder.False<User>();

foreach(var amico in amiciParsed)
{
    var a1 = amico; // Prevent modified closure (pre .Net 4.5)
    predicate = predicate.Or(user => user.Nome == a1.first_name 
                                  && user.Cognome == a1.last_name);
}

var query = db.User.Where(predicate.Expand())
              .OrderBy(p => p.Nome)
              ...

The advantage is that indexes on Nome and Cognome can be used (which is impossible if you search on a concatenated value). On the other hand, the number of OR clauses can get very large, which may hit certain limits in SQL Server (https://stackoverflow.com/a/1869810/861716). You'll have to stress-test this (although the same goes for IN clauses).

score 0 · Answer 3 · answered Nov 08 '13 at 22:49

When asking a question here on SO, you may want to translate it to English - don't expect people to know what "uente", "amico" or "Cognome" are.

One question: Why do you use ..in db.User.AsEnumerable() and not just ..in db.User?

Let everything in your query stay IQueryable (instead of IEnumerable). This lets Linq2Sql create SQLs that are as optimized as possible, instead of downloading all the records and joining the records client-side. This may also be the reason your search turns case-sensitive. Client-side in-memory string comparison will always be case-sensitive, while string comparison in SQL depends on the database's configuration.

Try ditching the .AsEnumerable() and see if you get better results:

utenti = (from User foo in db.User
          join bar in amiciParsed 
          ...

What is the way to join these two list?

3 Answers3