Results 1 to 13 of 13
  1. #1
    Join Date
    May 2012
    Posts
    14

    Unanswered: LINQ to SQL Query

    I have a stored procedure that I am trying to convert over to linq, but am having some issue. Here is my stored procedure:

    SELECT Contracts_tbl.ID, Contracts_tbl.ContractID, Contracts_tbl.Description, Contracts_tbl.DateBegin, Contracts_tbl.Quantity, Contracts_tbl.Price, Contracts_tbl.Active, Contracts_tbl.Reconciled, Ingredients_tbl.IngredientID, Ingredients_tbl.Description, Clients_tbl.ClientID, SUM(Invoice_Details_tbl.InvoicedAmount)
    FROM Contracts_tbl
    JOIN Clients_tbl ON Clients_tbl.ID = Contracts_tbl.ClientID
    JOIN Ingredients_tbl ON Ingredients_tbl.ID = Contracts_tbl.IngredientID
    LEFT OUTER JOIN Invoice_Details_tbl ON Invoice_Details_tbl.ContractID = Contracts_tbl.ID
    GROUP BY Contracts_tbl.ID, Contracts_tbl.ContractID, Contracts_tbl.Description, Contracts_tbl.DateBegin, Contracts_tbl.Quantity, Contracts_tbl.Price, Contracts_tbl.Active, Contracts_tbl.Reconciled, Ingredients_tbl.IngredientID, Ingredients_tbl.Description, Clients_tbl.ClientID
    ORDER BY Contracts_tbl.DateBegin

    This is what I have so far:

    querableSource = From con In dbcontracts.Contracts_tbls _
    Join clt In dbcontracts.Clients_tbls On clt.ID Equals con.ClientID _
    Join ing In dbcontracts.Ingredients_tbls On ing.ID Equals con.IngredientID
    Join invdtl In dbcontracts.Invoice_Details_tbls On invdtl.ContractID Equals con.ID
    Order By con.DateBegin Ascending _
    Select con.ID, con.ContractID, con.Description, clt.ClientID, ing.IngredientID, IngredientDescription = ing.Description, con.DateBegin, con.Quantity, invdtl.InvoicedAmount, con.Price, con.Active, con.Reconciled, con.Notes

    The issues I am having is how to create a LEFT OUTER JOIN between the Contracts_tbl and Invoice_Details_tbl so it returns null(or better yet, 0) when there is no records to sum. And actually perform the SUM of the InvoicedAmount column of the Invoice_Details_tbl.

    Thanks,
    jmayer

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jmayer View Post
    I have a stored procedure that I am trying to convert over to linq...
    Somebody holding a gun to your head?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2012
    Posts
    14
    What is that suppose to mean?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why not continue using the stored procedure, and call it from your app?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2012
    Posts
    14
    Because of performance. I am dealing with millions of records. I am using a third party control that supports server mode, meaning only the data that fits on the screen is returned until you scroll then the processing is taken care of on the server and displayed. This enhances the performance greatly when dealing with large chunks of data. The datasource of this control can only accept a linq to sql query.

    Thanks,
    jmayer

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure, but I think LINQ can use stored procedures, too.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You aren't going to get better performance than a stored procedure.
    Are you looking to implement windowing on your data?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2012
    Posts
    14
    yes linq does support stored procedures, but the 3rd party control I am using doesn't. it strictly accepts a linq query and binds it to a grid to give you better performance on large data tables. The windowing you speak of is taken care of in the control. Here is a link to an example of how the control works.

    Example E2805 - How to bind LinqInstantFeedbackSource to GridControl : DevExpress - Support Center

    Thanks,
    jmayer

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Save yourself some headache and use a view. Linq doesn't know any better and will treat it as an entity just the same as a table. That is by far the easiest way to get this done, assuming you don't need to use it as anything but a read-only IQueryable. In this case you'd have some issues trying to modify the result set if it was a "straight" l2s query anyway...

    It can be done with an inline query but uhhh... I wouldn't bother going down that path unless you absolutely have to. There's the up front cost of getting it right in the first place (I'll be blunt, I don't feel like trying that one), then there's also maintanance and debugging. You'll be glad you stored non-trivial queries as SQL Server objects instead of inline L2S when it comes time to revisit that code later. L2S is nice for the free grunt work but your "heavy" stuff belongs server-side whenever possible.

    I'd sooner modify the control itself than make it chew on an unwieldy L2S query. DevExpress controls tend to play well with customization/extension. It would probably end up being cheaper in the long run.
    Last edited by Teddy; 06-01-12 at 01:34.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    May 2012
    Posts
    14
    Thanks for all the input. I found a nice little tool called linqer Linqer | SQL to LINQ converter it allows you to enter your sql code and it spits out your linq for you. With a few minor changes, this is what I came up with:

    From contracts_tbls In dbcontracts.Contracts_tbls _
    Join clients_tbls In dbcontracts.Clients_tbls On New With {.ID = contracts_tbls.ClientID} Equals New With {.ID = clients_tbls.ID} _
    Join ingredients_tbls In dbcontracts.Ingredients_tbls On New With {.ID = contracts_tbls.IngredientID} Equals New With {.ID = ingredients_tbls.ID} _
    Group Join invoice_details_tbls In dbcontracts.Invoice_Details_tbls On New With {.ContractID = contracts_tbls.ID} Equals New With {.ContractID = invoice_details_tbls.ContractID} Into invoice_details_tbls_join = Group _
    From invoice_details_tbls In invoice_details_tbls_join.DefaultIfEmpty() _
    Group New With {contracts_tbls, ingredients_tbls, clients_tbls, invoice_details_tbls} By _
    contracts_tbls.ID, _
    contracts_tbls.ContractID, _
    contracts_tbls.Description, _
    contracts_tbls.DateBegin, _
    contracts_tbls.Quantity, _
    contracts_tbls.Price, _
    contracts_tbls.Active, _
    contracts_tbls.Reconciled, _
    ingredients_tbls.IngredientID, _
    Column1 = ingredients_tbls.Description, _
    clients_tbls.ClientID _
    Into g = Group _
    Order By _
    DateBegin _
    Select _
    ID = CType(ID, Int32?), _
    ContractID, _
    Description, _
    ClientID, _
    IngredientID, _
    Ingredient = Column1, _
    DateBegin, _
    Quantity, _
    Status = CType(g.Sum(Function(p) p.invoice_details_tbls.InvoicedAmount), Decimal?), _
    Price, _
    Active, _
    Reconciled

    Thanks again

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's nasty. I would hunt you down and smother you in your sleep if tasked with maintaining that months/years after you moved on to a new company.

    Don't say I didn't warn you.

    No seriously, that's going to be pretty much impossible to understand six months from now. The IL it's going to generate is not going to be anything close to efficient, even if the sql it generates is reasonable. Don't do everything in LINQ just because you can. Use it where it works and use the DB's features where they make sense.
    Last edited by Teddy; 06-13-12 at 18:54.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    May 2012
    Posts
    14
    I don't think it is too bad. Maintainence on this is going to be slim to none. With the controls i am using, to utilize the server mode along with other features I need to feed it a linq query.

    Thanks,
    jmayer

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    hehe... No maintenance eh? Famous last words!

    I'd still swap that to a view.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •