"LIKE" operator in LINQ to SQL?

[teddysnips]

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

 

[Jon Skeet [C# MVP]]

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!

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.

 

[Marc Gravell]

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:

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

 

[Peter Morris]

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

 

[Alun Harford]

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

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