SQL Linq, select from select and join

jkirkerx

Yes I did write this, I must of been on a roll. I understand what I wrote in TSQL, but I just can't wrap my head around how to write it in Linq. TSQL

DECLARE @startDate AS DATETIME, @stopDate AS DATETIME;
SET @startDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999);
SELECT
TOP 20
CartID
, PartNumber
, Thumbnail
, SDescription
, Qty
, Price
, Category
, Date
, ProductID
FROM
(
SELECT
sc.CartID
, sc.PartNumber
, sc.Thumbnail
, sc.SDescription
, sc.Qty
, sc.Price
, sc.Category
, sc.Date
, pi.productID
FROM ShoppingCart sc
LEFT JOIN PRODUCTINFO pi ON sc.PartNumber = pi.PartNumber
WHERE sc.Date > @startDate
AND sc.Date < @stopDate
) x
ORDER BY Date

Now I wrote this earlier as pResults = from Shopping cart with a join to products, so I can get the productID from products based on partNumber. But the join or something was inconsistent according to the error. I was running 2 context. I put the product and cart tables in separate context. So I was thinking perhaps I need to grab the Shopping cart data first, and then query those results with a join to products for the productID. I have no clue how to phrase this. This is what I have so far

Dim pValue As Integer = 0
Dim DateStart As New Date(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
Dim DateStop As New Date(Now.Year, Now.Month, Now.Day, 23, 59, 59, 0)

    Dim productContext As New ProductContext()
    Dim shoppingContext As New ShoppingCartContext()

    Dim query = \_
    (
        From sc In shoppingContext.ShoppingCart
        Where sc.CartDate >= DateStart \_
        And sc.CartDate <= DateStop
        Take 20
        Select
        {
            sc.CartID,
            sc.PartNumber,
            sc.ThumbNail,
            sc.SDescription,
            sc.Qty,
            sc.Price,
            sc.Category,
            sc.CartDate
        }
    ).AsEnumerable()

In hindsight, I should of used the productID in the shoppingCart table.

Richard Deeming

By creating two different DbContext classes, you're making things much harder for yourself. Entity Framework can only generate SQL queries for sets in a single context; as soon as you need to join to a set in a different context, you have to pull all of the data into memory first. Based on your SQL query, both tables are in the same database, so it would make much more sense to have both sets in the same context. That way, you can join them together properly, and Entity Framework will generate more efficient queries.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

I was thinking about that last night. Guess I should of made 1 giant context for the entire database?, or done a better job of planning by combining the cart and product tables. Lesson learned.

Richard Deeming

jkirkerx wrote:

Guess I should of made 1 giant context for the entire database?

That's the way I'd go. I'd only consider splitting them up if the tables in each context were completely separate, with no cross-context relationships, and would never need to be queried together.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

I just made 1 context file with everything in it. Finally got the DateRanges working, was banging my head against the wall to figure it out. I had to wrap the Where statement in (), in VB its different to compare.

Where (oh.OrderDate >= startDate And oh.OrderDate <= stopDate)

Hey thanks for your help!, that was hard to figure out.