Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Search two columns in LINQ to SQL?

I am trying to make a simple search method using LINQ to SQL in Visual Studio. In my database, I have the fields "Firstname" and "Lastname", and my search string is "name". How can I make a simple LINQ query which searches both fields?

In plain SQL I would do something like this:

SELECT (Firstname + Lastname) as 'Fullname'
FROM   table
WHERE  Fullname LIKE '%searchstring%'
like image 210
user709712 Avatar asked Apr 15 '11 11:04

user709712


2 Answers

The usual thing to do is this:

var users =
    from user in db.Users
    where user.FirstName.Contains(searchString) ||
        user.LastName.Contains(searchString)
    select user;

This however is not equivalent to your SQL query. The following is equivalent:

var users =
    from user in db.Users
    let fullName = user.FirstName + user.LastName
    where fullName.Contains(searchString)
    select user;
like image 124
Steven Avatar answered Sep 22 '22 02:09

Steven


var result = from p in db.Table
             let fullname = p.FirstName + " " + p.Lastname
             where fullname.Contains(searchString)
             select new { Fullname = fullname };
like image 33
Phill Avatar answered Sep 20 '22 02:09

Phill