CSharp/C#, "LIKE" operator in LINQ to SQL?

microsoft.public.dotnet.languages.csharp - General discussion regarding the C# .NET programming language. including best practices on developing with C# in VS, documentation, setup, and samples.


CSharp/C# >> "LIKE" operator in LINQ to SQL?

by teddysnips » Wed, 10 Sep 2008 21:45:54 GMT

I am just getting to grips with LINQ to SQL and my first attempt is to
create a Search form.

I need the LINQ to generate SQL like this:

SELECT * FROM CustomerTable WHERE Surname LIKE 'S%'

My LINQ so far looks like this:

var cons = from c in db.CustomerTable
where c.Surname LIKE 'S%'
select c;

However, it doesn't like "LIKE". How might I mimic this in LINQ? I
should add that I'm hardly the world's most experienced C# programmer
either!

Thanks

Edward
Top

CSharp/C# >> "LIKE" operator in LINQ to SQL?

by Jon Skeet [C# MVP] » Wed, 10 Sep 2008 21:54:38 GMT



No, it wouldn't like "LIKE" - you're not actually writing SQL here,
you're writing C# which is translated into SQL.

The C#/.NET way of checking whether one string starts with another is
to use the StartsWith method. Try changing your query to:

var cons = from c in db.CustomerTable
where c.Surname.StartsWith("S")
select c;


It may be worth noting that if you're only using one or two query
operators, the normal C# syntax can often end up being simpler:

var cons = db.CustomerTable.Where(c => c.Surname.StartsWith("S"));

That's basically what the compiler was doing anyway.

--
Jon Skeet - < XXXX@XXXXX.COM >
Web site: http://www.pobox.com/ ~skeet
Blog: http://www.msmvps.com/jon.skeet
C# in Depth: http://csharpindepth.com
Top

CSharp/C# >> "LIKE" operator in LINQ to SQL?

by Marc Gravell » Wed, 10 Sep 2008 23:09:33 GMT

> It may be worth noting that if you're only using one or two query

That is *especially* true of search forms (from the OP), as you can
compose multiple (optional) restrictions more easily:

IQueryable<Foo> query = db.CustomerTable;
if(!string.IsNullOrEmpty(surname))
{
query = query.Where(c => c.Surname.StartWith(surname));
}
if(!string.IsNullOrEmpty(forename))
{
query = query.Where(c => c.Surname.StartWith(forename));
}
if(dob != null) // assumes DateTime? for dob
{
DateTime dobActual = dob.Value.Date;
query = query.Where(c => c.DateOfBirth == dobActual);
}
// etc

Marc
Top

CSharp/C# >> "LIKE" operator in LINQ to SQL?

by Peter Morris » Wed, 10 Sep 2008 23:18:22 GMT

No more having to append strings ending with "and" and then having to trim
off the last 4 chars of the query string when you have finished building it
:-)
Top

CSharp/C# >> "LIKE" operator in LINQ to SQL?

by Alun Harford » Thu, 11 Sep 2008 01:54:09 GMT


Meh. My ORM has done that for me for many years :-)

The real advantage is:

No more having to maintain code where somebody keeps appending strings
ending with "and" and then having to trim off the last 4 chars of the
query string when they have finished building it.

I think that's still some years away though.

Alun Harford
Top
Similar Threads

1. Combination of Linq to sql and Linq to xml - CSharp/C#

2. Linq to SQL - Return DataTable as a result of Linq query 

Hi!

How to get result od dataTable from Linq query? I have typied DataSet
and I want to join couple of tables. And I have a problem with change
this result to DataTable type. (I don't want to rewrite everything in
foreach)

e.g. How can I make DataTable from it? Thx for help!

 var query =
from firmy in boKontakty.DataSetKontakty.FIRMY
                      join kancelarie in
boKontakty.DataSetKontakty.KANCELARIE on firmy.FIRMA_O equals
kancelarie.ID into firmKan
                      join kategorie in
boKontakty.DataSetKontakty.KATEGORIE on firmy.KATEGORIA equals
kategorie.NAZWA into firmKanKat
                      select
                      new
                      {
                          Id = firmy.ID,
                          Nazwa = firmy.NAZWA,
                          Nazwa_pelna = firmy.NAZWA_PELNA,
                          Kancelaria_obslugujaca = kancelarie.NAZWA,
                          Kolor = kategorie.KOLOR
                      };

3. Mixing linq to sql and linq to xml (in linqpad) - CSharp/C#

4. Question about LinQ (LinQ to Sql)

5. Linq 2 sql, enum, no supported translation to SQL - CSharp/C#

6. LINQ to SQL + SQL Server 7 

Hello,

I am trying to work with MS SQL Server 7 from the release version of
Visual Studio 2008 + LINQ to SQL. And the problem is that the LINQ to
SQL designer doesn't accept my tables saying that my connection
provider is unsupported. Then I found out that LINQ to SQL officially
supports only .NET Provider for SQL Server. The problem is that SQL
Server 7 can't work with this provider, so I have to use the .NET
Provider for OLE DB. Well, at least, ServerExplorer can't connect to
SQL Server 7 using .NET Provider for SQL Server...

Now the interesting thing is that this is a designer's problem only!
Actually LINQ to SQL do can communicate with SQL Server 7 without any
problems, including inserting data. I've implemented my data context
without using the designer, as follows:

class MyDataContext : System.Data.Linq.DataContext
{
   public System.Data.Linq.Table<CUSTOMER> CUSTOMERs;

   [System.Diagnostics.DebuggerNonUserCodeAttribute()]
   public MyDataContext() :
      base(new System.Data.SqlClient.SqlConnection("Data
Source=10.1.1.1;Persist Security Info=True;Password=111;User
ID=111;Initial Catalog=DEVELOP_DEKEL_PUBLISH"))
   {
   }
}

Please note that I am using here System.Data.SqlClient.SqlConnection.
And this works excellent.
The only problem is that I do that manually, I can't use the
designer...

So, ServerExplorer can only connect to the db using .NET Provider for
OLE DB while LINQ itself can use the SQL Provider without any
problems. And therefore the only thing that I need to do is to
convince the LINQ designer that it actually CAN handle my db =)

Can anybody offer some idea?

7. Linq Distinct() in Linq to Sql - ADO.Net

8. In Orcas Linq, When does it happen to convert LINQ to SQL command 

Ho folks, 
         In Orcas LINQ,  when does it happen to convert LINQ to SQL command? 
 Waht role does the conversion - runtime, compiling time...and where is the 
SQL command? Please advise. Thanks.
PeterK