how to use 'Like' in this linq query?

  • Thread starter Thread starter Rich P
  • Start date Start date
R

Rich P

--pseudo code here with the Like keyword -- how to apply it?

var query1 = from DataRow myRow in ds1.Tables["tblA"].Rows
where (string)myRow["Company"] Like "test"
select myRow;


I found this sample on the net:

list = list.Where(o => SqlMethods.Like(o.FirstName, FirstName))

How could I apply something like this to the query above?

Rich
 
I forgot to include the wildcards:

var query1 = from DataRow myRow in ds1.Tables["tblA"].Rows
where (string)myRow["Company"] Like "%test%"
select myRow;

how to handle 'Like' and wildcards with linq?

note: this query works if it is a straight '=='

Thanks

Rich
 
I added a reference to System.Data.Linq

and a

using System.Data.Linq.SqlClient;

and attempted the following query:

var query1 = from DataRow myRow in ds1.Tables["tblA"].Rows
where SqlMethods.Like(myRow["Company"], "%test%")
select myRow;


In the error message that ensued -- it mentioned that SqlMethods
currently only works with Linq To Sql. I guess this is Linq to a
Datatable. The rest of the error said that I had some invalid argument
--

perhaps myRow["Company"] ?


Rich
 
Rich P said:
var query1 = from DataRow myRow in ds1.Tables["tblA"].Rows
where (string)myRow["Company"] Like "%test%"
select myRow;
Click to expand...

You can express the preceding with the "Contains" method:

var query1 = from myRow in ds1.Tables["tblA"].AsEnumerable()
where myRow.Field<string>("Company").Contains("test")
select myRow:

If you only want the "%" at the end, you can use "StartsWith" instead of
"Contains".
 
Thanks. I will give that a try. Although, in my searching, other
articles suggested that Contains does not handle wildcards. I guess I
will find out.

Thanks again for the reply and suggestion.

Rich
 
Rich said:
I added a reference to System.Data.Linq

and a

using System.Data.Linq.SqlClient;

and attempted the following query:

var query1 = from DataRow myRow in ds1.Tables["tblA"].Rows
where SqlMethods.Like(myRow["Company"], "%test%")
select myRow;


In the error message that ensued -- it mentioned that SqlMethods
currently only works with Linq To Sql. I guess this is Linq to a
Datatable. The rest of the error said that I had some invalid argument
Click to expand...

It's going to be this I believe.

myRow.Contains("something")
 
Thanks. I will give that a try. Although, in my searching, other
articles suggested that Contains does not handle wildcards. I guess I
will find out.
Click to expand...

LIKE '%foobar%' is .Contains("foobar"), but for more
advanced usages of LIKE you will need to do something
like a selecting where a lambda using regex returns
true.

Arne
 
I attempted something like this:

list = list.Where(o => SqlMethods.Like(o.FirstName, FirstName))


but the error message said that SqlMethods.Like only works with Linq to
Sql. I am trying to use Linq on a dataTable in my C# (2008) winform
app. Any thoughts appreciated if there is a workaround for using
SqlMethods against a dataTable contained in the app.

Rich
 
Rich said:
I attempted something like this:

list = list.Where(o => SqlMethods.Like(o.FirstName, FirstName))


but the error message said that SqlMethods.Like only works with Linq to
Sql. I am trying to use Linq on a dataTable in my C# (2008) winform
app. Any thoughts appreciated if there is a workaround for using
SqlMethods against a dataTable contained in the app.
Click to expand...

You can't use the statement as it only pertains to querying a SQL Server
table on the entity model using Linq.

http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.like.aspx
 
Rich said:
I attempted something like this:

list = list.Where(o => SqlMethods.Like(o.FirstName, FirstName))


but the error message said that SqlMethods.Like only works with Linq to
Sql. I am trying to use Linq on a dataTable in my C# (2008) winform
app. Any thoughts appreciated if there is a workaround for using
SqlMethods against a dataTable contained in the app.
Click to expand...

And the workaround is to use the Contains function in the Linq query
against the row and column in the row.
 
I attempted something like this:

list = list.Where(o => SqlMethods.Like(o.FirstName, FirstName))

but the error message said that SqlMethods.Like only works with Linq to
Sql. I am trying to use Linq on a dataTable in my C# (2008) winform
app. Any thoughts appreciated if there is a workaround for using
SqlMethods against a dataTable contained in the app.
Click to expand...

If available memory and performance allows it then just load
everything and use regex.

See example below.

Arne

==========================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Linq.SqlClient;
using System.Text.RegularExpressions;

namespace E
{
[Table(Name="T1")]
public class T1
{
[Column(Name="F1",IsPrimaryKey=true)]
public int f1;
[Column(Name="F2")]
public string f2;
}

public class Program
{
public static void Main(string[] args)
{
using(SqlConnection con = new
SqlConnection(@"Server=ARNEPC3\SQLEXPRESS2008;Integrated
Security=SSPI;Database=Test"))
{
DataContext db = new DataContext(con);
Table<T1> t1 = db.GetTable<T1>();
Console.WriteLine("Contains:");
IEnumerable<T1> a = t1.Where(r => r.f2.Contains("B"));
foreach(T1 r in a)
{
Console.WriteLine(r.f1 + " " + r.f2);
}
Console.WriteLine("Regex:");
IEnumerable<T1> b = t1.ToList().Where(r =>
Regex.IsMatch(r.f2, "^.*B.*$"));
foreach(T1 r in b)
{
Console.WriteLine(r.f1 + " " + r.f2);
}
Console.ReadKey();
}
}
}
}
 
Back
Top