none
How to Search two columns in table as if they were one RRS feed

  • Question

  • Hello,

    I have users table with fist_name and last_name column. How Can I Search two columns in table  as if they were one ?

    Thanks,
    Marcin


    Monday, May 24, 2010 12:36 AM

Answers

  • What do you mean by search two columns as if they're one?

    You can:

    a) match on a partial name in either one - this will generate a SQL where clause along the lines "where firstname like 'John%' or lastname like 'John%'":

    string someName = "John";

    IQueryable<Person> ppl = from p in dc.People
    where p.FirstName.StartsWith(someName) || p.LastName.StartsWith(someName)
    select p;

    b) concatenate the columns db-side - note that this will result in a query that can not use indexes that may exist on the two columns (always full table scan):

    var someName = "John Doe";

    IQueryable<Person> ppl = from p in dc.People
    where p.FirstName + " " + p.LastName == someName
    select p;

    c) a variation of (a) that is more optimizer friendly (if your real-world-query is slightly more complicated than a single table select):

    string someName = "John";

    IQueryable<Person> ppl = (from p in dc.People where p.FirstName.StartsWith(someName) select p).Union(from p in dc.People where p.LastName.StartsWith(someName) select p);


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Marked as answer by czetyr Monday, May 24, 2010 1:34 PM
    Monday, May 24, 2010 3:18 AM
    Answerer
  •  

    Thanks for your help. I wanted to check if firstNameString + lastNameString contains paternString :

    IQueryable<persons> ppl = from p in ctx.persons
                                            where (p.first_name + " " + p.last_name).ToLower().Contains(patern.ToLower())
                                            select p;

    It works fine until patern string contains polish characters. I don't know where I've made a mistake :

    in sql database , entity framework or in linq query  


    • Marked as answer by czetyr Monday, May 24, 2010 1:34 PM
    Monday, May 24, 2010 10:44 AM

All replies

  • What do you mean by search two columns as if they're one?

    You can:

    a) match on a partial name in either one - this will generate a SQL where clause along the lines "where firstname like 'John%' or lastname like 'John%'":

    string someName = "John";

    IQueryable<Person> ppl = from p in dc.People
    where p.FirstName.StartsWith(someName) || p.LastName.StartsWith(someName)
    select p;

    b) concatenate the columns db-side - note that this will result in a query that can not use indexes that may exist on the two columns (always full table scan):

    var someName = "John Doe";

    IQueryable<Person> ppl = from p in dc.People
    where p.FirstName + " " + p.LastName == someName
    select p;

    c) a variation of (a) that is more optimizer friendly (if your real-world-query is slightly more complicated than a single table select):

    string someName = "John";

    IQueryable<Person> ppl = (from p in dc.People where p.FirstName.StartsWith(someName) select p).Union(from p in dc.People where p.LastName.StartsWith(someName) select p);


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Marked as answer by czetyr Monday, May 24, 2010 1:34 PM
    Monday, May 24, 2010 3:18 AM
    Answerer
  •  

    Thanks for your help. I wanted to check if firstNameString + lastNameString contains paternString :

    IQueryable<persons> ppl = from p in ctx.persons
                                            where (p.first_name + " " + p.last_name).ToLower().Contains(patern.ToLower())
                                            select p;

    It works fine until patern string contains polish characters. I don't know where I've made a mistake :

    in sql database , entity framework or in linq query  


    • Marked as answer by czetyr Monday, May 24, 2010 1:34 PM
    Monday, May 24, 2010 10:44 AM
  • See my comments in your other thread ( http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/ae8e5b05-a92f-46c3-9f56-737fd18fa6f1 ).

    As I mentioned before, be aware that concatenating db fields in the where clause makes it impossible for SQL server to use any indexes. If the table can be large you may want to split the pattern string instead and do something like in (a) or (c) in my reply above, to avoid table scans.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, May 24, 2010 2:32 PM
    Answerer
  • nice example.
    Thursday, February 8, 2018 12:42 PM
  • declare @t table 
    (name nchar(10),
     fname nchar(10))
     insert into @t (name,fname)
     values ('A','B'),('B','C')
     select *from @t where name = 'A' or fname='A'

    please first learn T-sql then write code Linq Sql

    use that positions "OR" syntax sql

    Saturday, March 10, 2018 9:46 PM