none
Dynamic where condition RRS feed

  • Question

  • Hi all,

    I need to add dynamic where conditions to EF Linq query by checking some condtions,i've checked with forums but most of answered links not working ( ex :- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=104435&SiteID=1 ).this is my query

    var QryRA = from billheaders in
                (from billheaders in DB.BillHeaders
                where billheaders.DocType == "RA"
                select new
                {
                 billheaders.SubTotal,
                 Dummy = "x"
                })
                group billheaders by new { billheaders.Dummy } into g
                select new
                {
                 Count = (Int64?)g.Count(),
                 Amount = (Decimal?)g.Sum(p => p.SubTotal)
                };

    I need to check condtion and if it's true add another where condtion like thisif

    if (lCnt > 0 )
    { query = query + "and shiftid = 2"; } // like sql

    ** Previously i've done this type of thing but it needs to include the filtering field on select clause, but in this case i cannot do it,since this is using aggregate functions like count & sum.

    Pls advice me if any body have done this before

    Thanks & Regards,

    dimuthu


    Dimuthu

    Wednesday, January 9, 2013 7:17 AM

Answers

  • Hi,

    You could perfectly have first a q1 query where you'll add your where clauses as you seems to do usually and then just start from the q1 query to construct your final q2 query that will perform the final grouping...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 7:51 AM
    Wednesday, January 9, 2013 12:15 PM

All replies

  • Hi,
    actually as you were thinking like raw SQL queries with dynamic where condition, that is not possible in LINQ and more interesting that's good.
    That was an extra headache, inserting multiple ifs in string concatination.
    Probably you are unaware of a reality,that's way you are asking this.
    Let me make this clear to you.
    When you write any LINQ query, it does not hit the database unless it is required.
    Now suppose you wrote a LINQ as you gave in your post,so that LINQ query will not hit the database even at runtime unless it is required to.
    It will hit the databse when you are databinding or iterating on the final output of the LINQ query.

    So,consider these queries :

    if (lCnt < 0 )
    {
    var QryRA1 = from billheaders in
                (from billheaders in DB.BillHeaders
                where billheaders.DocType == "RA"
                select new
                {
                 billheaders.SubTotal,
                 Dummy = "x"
                })
                group billheaders by new { billheaders.Dummy } into g
                select new
                {
                 Count = (Int64?)g.Count(),
                 Amount = (Decimal?)g.Sum(p => p.SubTotal)
                };
    // Do further steps
    }
    else
    {
    var QryRA2 = from billheaders in
                (from billheaders in DB.BillHeaders
                where billheaders.DocType == "RA" && billheaders.shiftid == 2
                select new
                {
                 billheaders.SubTotal,
                 Dummy = "x"
                })
                group billheaders by new { billheaders.Dummy } into g
                select new
                {
                 Count = (Int64?)g.Count(),
                 Amount = (Decimal?)g.Sum(p => p.SubTotal)
                };
    // Do further steps
    }

    According to condition any one query will get evaluated and finally it will hit the database.
    Do you have any problem in this?


    One good question is equivalent to ten best answers.

    Wednesday, January 9, 2013 7:40 AM
  • Hi shyam,

    thx for ur idea,the thing is i'm having about 150 queries on this function, if we do like this we have to have queries = 150 * condtions that means in this place 300 queries , i dont think this is smart.pls see below query , i'm looking for that type of solution.but it needs to include the filtering field on select clause, but in this case i cannot do it,since this is using aggregate functions like count & sum.

    var qryRnHeader = (from txn in DB.RnHeaders
                      join loc in DB.Locations on txn.FromWarehouseId equals loc.Id
                      where (txn.CreatedBy == ObjTxnSearch.CreatedBy)
                      select new
                      {
                           DocNo = txn.DocNo,
                           DcType = "Requisition Note",
                           Location = loc.Description1,
                           Date = txn.DocDate,
                           txn.FromWarehouseId,
                           txn.DocDate,
                           txn.Remarks
    
                       });
    
                       if ((!string.IsNullOrEmpty(ObjTxnSearch.DocNo)))
                       {
                           qryRnHeader = qryRnHeader.Where(t => t.DocNo.Contains(ObjTxnSearch.DocNo));
                        }

    Thanks & Regards,

    Dimuthu


    Dimuthu

    Wednesday, January 9, 2013 8:00 AM
  • Hi,
    where the smartness gone when someone written 150 queries?
    As far as i know it is not possible to insert if condition in LINQ query to make it dynamic like raw SQL where you insert if condition in string concatination.
    If you are really challenging for smartness, challenge that person who has written 150 query to turn it to 5 queries.

    One good question is equivalent to ten best answers.

    Wednesday, January 9, 2013 8:33 AM
  • Hi,

    You could perfectly have first a q1 query where you'll add your where clauses as you seems to do usually and then just start from the q1 query to construct your final q2 query that will perform the final grouping...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 7:51 AM
    Wednesday, January 9, 2013 12:15 PM