Using "Like" in LINQ to SQL

I've recently discovered that LINQ and VB can be confusing. I was attempting to pull the top 10 rows of a table where the column started with 'W'. You'd think after I typed that, I'd figure out the solution. That's not the case. Instead, I struggled. Here's what I originally came up with:

Dim q = From t In db.Employees Where t.LastName Like prefixText & "%" Select t.LastName Take count

Little did I realize that the Like operator in my query was the VB Like operator. So, I had to modify my lambda expression to this:

Dim q = From t In db.Employees Where t.LastName.StartsWith(prefixText) Select t.LastName Take count

You'll notice that StartsWith makes more sense. <sigh> If only VB were...

13 Comments

  • In case you had not found out .Contains is the equivalent to %String%. Like you it took a while to figure these things out.

  • For those times that you really DO need to do a real "Like", and you know you're talking to SQL, there's SqlMethods.Like. For example, when a user is free to add wildcards to a search field. You don't know whether to do a StartsWith, EndsWith, or Contains. Here's an example.

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

  • I have a little problem I'm a beginner in LINQ to SQL and I've follow the tutorial from ScottGu's blog: http://weblogs.asp.net/scottgu/archive/2007/07/16/linq-to-sql-part-5-binding-ui-using-the-asp-linqdatasource-control.aspx

    All was ok I use VS 2008 (I've converted from VB.NET to C# , I use C#) but when I wish to put the code from App_Code in an sub dir like DAL or BLL (for extensibility of entities like products with partial clases) and generate again the dblm file I've receiving en error:

    1. first in my BLL dir from App_Code:

    public partial class Product
    {
    partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
    if (action == ChangeAction.Update && this.Discontinued == true && this.UnitsOnOrder > 0)
    {
    throw new ArgumentException("Record level can't be grather than 0 if Product is Discontinued");

    }
    }

    }

    and I've included the namespace : using DAL;

    ... but still error message is :No defining declaration found for implementing declaration of partial method 'Product.OnValidate(System.Data.Linq.ChangeAction)'

    2 If i comment all BLL code from my extensibility partial class Product I receive an general error:

    Could not load type 'NorthwindDataContext'.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Web.HttpException: Could not load type 'NorthwindDataContext'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:


    [HttpException (0x80004005): Could not load type 'NorthwindDataContext'.]
    System.Web.Compilation.BuildManager.GetType(String typeName, Boolean throwOnError, Boolean ignoreCase) +565
    System.Web.UI.WebControls.LinqDataSourceView.get_ContextType() +68

    [InvalidOperationException: Could not find the type specified in the ContextTypeName property of LinqDataSource 'CategoryLinqDS'.]
    System.Web.UI.WebControls.LinqDataSourceView.get_ContextType() +193
    System.Web.UI.WebControls.LinqDataSourceView.CreateContextAndTable() +458
    System.Web.UI.WebControls.LinqDataSourceView.EnsureContextAndTable(Boolean selecting) +39
    System.Web.UI.WebControls.LinqDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +421
    System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
    System.Web.UI.WebControls.ListControl.PerformSelect() +31
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
    System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
    System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
    System.Web.UI.Control.PreRenderRecursiveInternal() +86
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041




    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433




    Thanks in advance guys. :)




  • What if I need everything where "doctor"
    with LIKE it whould be:
    Dim q = From t In db.Employees Where t.LastName Like & "%" prefixText & "%" Select t.LastName

  • i have used in my project .it is working

  • ddddddddddddddddddd

  • How would you use 'like' in linq with a join??
    For example:
    From Table tabEs
    INNER JOIN tabCad orP on Es.ColumnID = orP.ColunaID
    INNER JOIN tabCad orF on orF.ColumnH like orP.ColunaH + '%'
    Where....


    Thanks.

  • If you want to use
    where firstName like '%text'
    Use firstName.StartsWith(text)

    where firstName like 'text%'
    Use firstName.EndsWith(text)

    where firstName like '%text%'
    Use firstName.Contains(text)

  • How to select unicode fields with 'N'

  • Hi, I would like search by '%word1%word2%', in SQL select * form xxxx where name like '%word1%word2%', but containd don't let me do that, anybody know the way without using predicatedbuilder, dynamiclinq, expressionstree, etc, etc etc, just 'like', like SQL, thanks a lot!

  • thanks a lot for this

  • System.Data.Linq.SqlClient namespace

    var query = from c in ctx.Customers

    where SqlMethods.Like(c.City, "L_n%")

    select c;

  • I have used Contains Method to work as a like function.
    var query = from c in ctx.Customers

    where c.coumn1.contains("L_n")

    select c;

Comments have been disabled for this content.