none
Count executing two times on database RRS feed

  • Question

  • Hello,

     

    I have this linq query

    Dim Products = From p In db.Products _
                          Where
    p.CategoryID > 2 And p.UnitPrice > 3 _
                          Order
    By p.ProductID _
                          Select
    p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit

    Dim TotalCount As Integer = Products.Count()
    GridView1.DataSource = Products.Skip(0).Take(10)
    GridView1.DataBind()

    This works fine but the problem is that linq is executing the select on database two times...

    I already did this

     

    Dim Products = From p In db.Products _
                          Where
    p.CategoryID > 2 And p.UnitPrice > 3 _
                          Order
    By p.ProductID _
                          Select
    p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit


    Dim List = Products.ToList()
    Dim TotalCount As Integer = List.Count()
    GridView1.DataSource = List.Skip(0).Take(10)
    GridView1.DataBind()

     

    Linq is executing the query on database one time, But the problem is that sql is bring to my application all the registers(200) remenber, i just want ten "Take(10)"

     

    Finnaly i just want to assign a variable inside linq instruction...something like this

    Dim CountVariable As Integer

    Dim Products = From p In db.Products _
                          Where
    p.CategoryID > 2 And p.UnitPrice > 3 _
                          Order
    By p.ProductID _
                          Select
    p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit, CountVariable = Count(*)

     

    Can anybody help me ?

    Wednesday, May 7, 2008 8:17 PM

Answers

  • Yes, repeat the clause there. Thanks for pointing that out!

     

    Dim Products = From p In db.Products

                           Where p.CategoryID > 2 And p.UnitPrice > 3 _

                           Order By p.ProductID _

                           Take 10 _

                           Select New With _

                           { _

                               .result = New With {p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit},

                               .count = (From prod In db.Products _

                                             Where prod.CategoryID > 2 And prod.UnitPrice > 3 _

                                             Select prod).Count() _

                          }

    Dim CombinedResult = Products .ToList()

    Dim Bind = From r In CombinedResult _

    Select r.result

    Dim Count = CombinedResult.First().count

     

    Thanks,

    Maurycy

     

    Thursday, May 8, 2008 7:30 PM
  • It's a trade off between doing what you want in two neat queries, or in one complex query. I would actually recommend doing it in two separate queries. With two queries, the intention is clear, and probably performance is better as well.

     

    Thanks,

    Maurycy

    Thursday, May 8, 2008 9:56 PM

All replies

  • You can do it in the following way:

     

    Dim Products = From p In db.Products _

                           Where p.CategoryID > 2 And p.UnitPrice > 3 _
                           Order By p.ProductID _

                           Take 10 _

                           Select New With

                           {

                               .result = New With {p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit},

                               .count = db.Configs.Count()

                           }

     

    Dim CombinedResult = Products .ToList()

    Dim Bind = From r In CombinedResult _

                     Select r.result

    Dim TotalCount = CombinedResult.First().count

     

    Basically, each element of the result also contains the total count of Products in database. You sent only 10 of them to the store, and on the client extract appropriate parts of the result (Bind is the results you are interested, TotalCount is the count of Products in the database)

     

    Thanks,

    Maurycy

    Thursday, May 8, 2008 1:52 AM
  • Hello Maurycy,

     

     

    From where did you take the property "Configs"
    DataContext class doesn't have this field

     

    Thursday, May 8, 2008 12:31 PM
  • My bad, I was using a different database to investigate and forgot to replace that one Wink

     

    it should be:

     

    .count = db.Products.Count()

     

     

    So the complete scenario would look like:

     

    Dim Products = From p In db.Products _

                           Where p.CategoryID > 2 And p.UnitPrice > 3 _
                           Order By p.ProductID _

                           Take 10 _

                           Select New With

                           {

                               .result = New With {p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit},

                               .count = db.Products.Count()

                           }

     

    Dim CombinedResult = Products .ToList()

    Dim Bind = From r In CombinedResult _

                     Select r.result

    Dim TotalCount = CombinedResult.First().count

     

    Thanks,

    Maurycy

     

    Thursday, May 8, 2008 5:57 PM
  • Hi,

     

    I think it's not going to work....

    My Query has a Where Clause and you are selecting the total of products (without the clause)

    Dim Products = From p In db.Products _

                           Where p.CategoryID > 2 And p.UnitPrice > 3 _
                           Order By p.ProductID _

                           Take 10 _

                           Select New With

                           {

                               .result = New With {p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit},

                               .count = db.Products.Count() (Do i have to repeat where clause here ??)

                           }

     

     

     

    Thursday, May 8, 2008 6:20 PM
  • Yes, repeat the clause there. Thanks for pointing that out!

     

    Dim Products = From p In db.Products

                           Where p.CategoryID > 2 And p.UnitPrice > 3 _

                           Order By p.ProductID _

                           Take 10 _

                           Select New With _

                           { _

                               .result = New With {p.ProductID, p.ProductName, p.UnitPrice, p.QuantityPerUnit},

                               .count = (From prod In db.Products _

                                             Where prod.CategoryID > 2 And prod.UnitPrice > 3 _

                                             Select prod).Count() _

                          }

    Dim CombinedResult = Products .ToList()

    Dim Bind = From r In CombinedResult _

    Select r.result

    Dim Count = CombinedResult.First().count

     

    Thanks,

    Maurycy

     

    Thursday, May 8, 2008 7:30 PM
  • Doesn't it a "Dirty" solution ???

    The where clause above is smaller but it could be much bigger....

    Thursday, May 8, 2008 7:37 PM
  • It's a trade off between doing what you want in two neat queries, or in one complex query. I would actually recommend doing it in two separate queries. With two queries, the intention is clear, and probably performance is better as well.

     

    Thanks,

    Maurycy

    Thursday, May 8, 2008 9:56 PM