none
What is the equivalent of SQL Keyword PatIndex to LINQ RRS feed

  • Question

  • select count(*) as usercount, ix.alias
            from [iis reporting].[dbo].internetlog l,
            [user] ix
            where ix.status = 1
            and ix.alias = substring(l.username, patindex('%\%',l.username)+1, (len(rtrim(l.username))-patindex('%\%',l.username)))
            and patindex('%\%',l.username)-1 > 0
            group by ix.alias

    Thank you.
    Tuesday, July 21, 2009 11:46 AM

All replies

  • For Linq-to-sql

    I added

       

    [Function(Name = "PATINDEX", IsComposable = true)]

    public int PATINDEX([Parameter(Name = "pattern", 

    DbType = "nVarChar(8000)")] string @pattern, [Parameter(Name = "expression", 

    DbType = "VarChar(100)")] string @expression)        {

    return ((int)(this.ExecuteMethodCall(this, 

    ((MethodInfo)(MethodInfo.GetCurrentMethod())), 

    @pattern, @expression).ReturnValue)); 

    }


    to my datacontext class

    and used it in code as
       
    result = result.Where(p => db.PATINDEX("%" + Email + "%", p.Email) > 0);

    Friday, August 2, 2013 1:10 PM
    1. Add Reference System.Data.Entity
    2. Add using System.Data.Objects.SqlClient;
    3. Do your query like
    var query = from t in db.TableName
    select new
    {
        value = SqlFunctions.PatIndex(patern,target)
    }


    Please Mark as Answer and Vote as Helpful if I helped.

    Also please visit my blog http://www.msguy.me/


    Wednesday, August 7, 2013 3:01 PM