CODEWITHSUNDEEP
c#sqllinqsql-likepredicatebuilder
Michael Jovi
Michael Jovi

Reputation: 53

How to build dynamic SQL by PredicateBuilder for this?

I'm using the PredicateBuilder as seen here http://www.albahari.com/nutshell/predicatebuilder.aspx, I want to build a LINQ for table, how to transform this transact-sql to LINQ?

SELECT * 
FROM Members 
WHERE Confirm=true 
AND CaseExist=true 
AND ( Username LIKE '%Jack%' OR Fullname LIKE '%Jack%' ) 
AND ( Tags IN ('3D','Photoshop','Java','Design') ) 
AND ( Catid+',' LIKE '%2,%' OR Catid+',' LIKE '%4,%' 
      OR Catid+',' LIKE '%5,%' OR Catid+',' LIKE '%21,%' ) 
ORDER BY Rate1 DESC, Rate2 DESC, Rate3 DESC, LatestLogin DESC, LatestUpdate DESC

I build this:

var predicate = PredicateBuilder.True<Member>();

if (!string.IsNullOrEmpty(SearchString))
{
    predicate = predicate.And(x =>
        x.Username.Contains(SearchString)
        || x.FullName.Contains(SearchString)
    );
}

predicate = predicate.And(x => x.Confirmed == true)
                     .And(x => x.CaseExist == true);

But I can't go on, so can anyone suggest me on how to do this?

Upvotes: 1

Views: 489

Answers (1)

Anton&#237;n Lejsek
Anton&#237;n Lejsek

Reputation: 6103

    string[] arr = new string[] { "3D", "Photoshop", "Java", "Design" };
    predicate = predicate.And(x => arr.Contains(x.Tags));

    var arr2 = new[] { "2", "4", "5", "21" };
    var predicateCatid = PredicateBuilder.False<Member>();
    foreach (var cat in arr2)
    {
        predicateCatid = predicateCatid.Or(x => (x.Catid + ",").Contains(cat + ","));               
    }
    predicate = predicate.And(predicateCatid);

I wonder why Tags can have only one tag in it.

Upvotes: 1

Related Questions