Database

21-Jul-15 13:27

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 Deeming22-Jul-15 1:50

Richard Deeming

22-Jul-15 1:50

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

jkirkerx22-Jul-15 6:02

22-Jul-15 6:02

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 Deeming22-Jul-15 8:00

Richard Deeming

22-Jul-15 8:00

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

jkirkerx22-Jul-15 9:04

Trouble working with dates again and union and sum

22-Jul-15 9:04

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.

jkirkerx20-Jul-15 13:08

Re: Trouble working with dates again and union and sum

20-Jul-15 13:08

So I wrote this to represent the TSQL below
Not sure where I messed up, but I think it's the sum at the end.
I got one result for $46.51, but it should of been $0.00, now I broke it.

Using context As New OrdersContext

  Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
  Dim DateStop As DateTime = DateTime.Today.AddDays(+1)

  pValue = _
      (From oh In context.Order_History
      Where oh.OrderStatus = "COMPLETED" _
      And oh.OrderDate > DateStart _
      And oh.OrderDate < DateStop
      Select oh).Union _
     (From oc In context.Order_Completed
     Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
     Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
     Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
     Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
     Or oc.OrderStatus = "READY_TO_SHIP" _
     And oc.OrderDate > DateStart _
     And oc.OrderDate < DateStop
    Select oc).Sum(Function(m) m.GrandTotal)

End Using

To represent this

DECLARE @StartDate AS Date; 
DECLARE @StopDate AS Date; 
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101); 
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101); 
WITH sums AS ( 
   SELECT SUM(GrandTotal) GrandTotal 
   FROM CompletedOrdersHistory 
   WHERE OrderStatus='COMPLETED' 
   AND OrderDate >= @StartDate 
   AND OrderDate < @StopDate  
   UNION ALL 
   SELECT SUM(GrandTotal) GrandTotal 
   FROM CompletedOrders 
   WHERE OrderStatus = 'QUEUED_FOR_FULFILLMENT' 
   OR OrderStatus = 'OUT_FOR_FULFILLMENT' 
   OR OrderStatus = 'QUEUED_FOR_SHIPPING' 
   OR OrderStatus = 'OUT_FOR_PACKAGING' 
   OR OrderStatus = 'READY_TO_SHIP' 
   AND OrderDate >= @StartDate 
   AND OrderDate < @StopDate 
) 
SELECT Sum(GrandTotal) AS GrandTotal 
FROM sums

jkirkerx20-Jul-15 13:34

20-Jul-15 13:34

Got the union working, and sum, just have trouble with the date range.

pValue = _
                ((From oh In context.Order_History
                Where oh.OrderStatus = "COMPLETED" _
                And oh.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
                Select oh.GrandTotal).Union _
                (From oc In context.Order_Completed
                Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                Or oc.OrderStatus = "READY_TO_SHIP" _
                And oc.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
                Select oc.GrandTotal)).Sum()

make nullable type

jkirkerx20-Jul-15 13:59

20-Jul-15 13:59

Well I'm closer now. I suspect that I have to redesign this Linq in order for it to work.
More of a From Sum Select then where

The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Dim pValue As Nullable(Of Decimal) = 0

        Using context As New OrdersContext

            Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
            Dim DateStop As DateTime = DateTime.Today.AddDays(+1)

            pValue = _
                ((From oh In context.Order_History
                Where oh.OrderDate >= DateStart _
                And oh.OrderDate <= DateStop _
                And oh.OrderStatus = "COMPLETED" _
                Select oh.GrandTotal).Union _
                (From oc In context.Order_Completed
                Where oc.OrderDate >= DateStart _
                And oc.OrderDate <= DateStop _
                And oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                Or oc.OrderStatus = "READY_TO_SHIP"
                Select oc.GrandTotal)).Sum()

        End Using

        Return pValue

[Got It]

jkirkerx21-Jul-15 9:58

The above post is incorrect - SQL Linq, Date Range, Union in VB

21-Jul-15 9:58

Finally

Using context As New OrdersContext

            pValue = _
                (
                    From oh In context.Order_History
                    Where oh.OrderDate >= DateStart _
                    And oh.OrderDate <= DateStop _
                    And oh.OrderStatus = "COMPLETED" _
                    Select oh.GrandTotal
                ).Union _
                (
                    From oc In context.Order_Completed
                    Where oc.OrderDate >= DateStart _
                    And oc.OrderDate <= DateStop _
                    And oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                    Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                    Or oc.OrderStatus = "READY_TO_SHIP"
                    Select oc.GrandTotal
                ).DefaultIfEmpty().Sum()

        End Using

jkirkerx22-Jul-15 8:54

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

22-Jul-15 8:54

After testing with a real dataset, I was getting the wrong values.
For doing this in VB, you have to wrap the date range in (), in order to properly evaluate it.
So this produces the correct result, even if the value is 0.
I was banging my head against the wall trying to figure this one out.

Thanks to Richard Deeming for setting me straight on the DateTime.

Dim pValue As Decimal = 0

        Dim baseDate As DateTime = DateTime.Today()
        Dim dateStart As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 0, 0, 0, 0)
        Dim dateStop As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 23, 59, 59, 999)

        Using context As New hx5Context

            pValue = _
            (
                From oh In context.Order_History
                Where (oh.OrderDate = dateStart And oh.OrderDate <= dateStop) _
                And Not (oh.OrderStatus = "ORDER_CANCELED")
                Select oh.GrandTotal
            ).Union _
            (
                From oc In context.Order_Completed
                Where (oc.OrderDate >= dateStart And oc.OrderDate <= dateStop) _
                And
                (
                    oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                    Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                    Or oc.OrderStatus = "READY_TO_SHIP" _
                )
                Select oc.GrandTotal
            ).DefaultIfEmpty().Sum()

        End Using

        Return pValue

Mycroft Holmes22-Jul-15 14:15

Mycroft Holmes

22-Jul-15 14:15

jkirkerx wrote:
Thanks to Richard Deeming for setting me straight on the DateTime.

You can up vote his responses to show appreciation (up/down arrows appear on the left of the response).

Never underestimate the power of human stupidity
RAH

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

jkirkerx23-Jul-15 6:38

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

23-Jul-15 6:38

I haven't been able to do that in years, its like they took that away from me. Seriously, I don't get the vote interface when I log in.

Brisingr Aerowing23-Jul-15 7:37

Brisingr Aerowing

23-Jul-15 7:37

Hover your mouse pointer over a message and two arrows should appear on the left side. The green upward pointing one is an upvote. The old voting system was removed due to abuse, but now a spam/abuse vote is used as a one vote by wuck fits and trolls. No real change there.

What do you get when you cross a joke with a rhetorical question?

The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.

Do questions with multiple question marks annoy you???

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

jkirkerx23-Jul-15 8:21

23-Jul-15 8:21

I've seen those, never bothered to click on them.
Thanks
Guess you need to delete the message to keep it secret secret!

Database management

Otekpo Emmanuel19-Jul-15 9:22

Otekpo Emmanuel

19-Jul-15 9:22

Hello friends! How is the day going? I need opinions in this case. I'm working on a social network site and as you all know there are many modules involved e.g friends, chat, messages table etc. My question is should I create database for each e.g chat db, messages db, friends db or I should create one database then create table for each of them e.g tblchat, tblmessages etc. Which of the option above is ok when managing social network?

PIEBALDconsult19-Jul-15 9:40

PIEBALDconsult

19-Jul-15 9:40

While possible, I expect that any of those options will prove equally challenging to you.

Richard MacCutchan19-Jul-15 22:00

Richard MacCutchan

19-Jul-15 22:00

You need to decide where one set of records needs a connection or relationship to another. If they have a relationship then it makes sense for them to be in separate tables of a single database.

Mycroft Holmes19-Jul-15 22:32

Mycroft Holmes

19-Jul-15 22:32

If you are expecting extreme volumes you should investigate the storage methods specifically for that environment. Otherwise have them all reside in the same database.

If you need separation then use schemas, this will make it easier to split the database in the future if your client base grows significantly.

Never underestimate the power of human stupidity
RAH

Wendelius20-Jul-15 6:50

Wendelius

20-Jul-15 6:50

As already answered the man criteria would be; Is there a relation between the data? If there is, a single database is the most logical choice.

However, there is also other things to consider such as maintenance, backups and so on. So even if the data isn't related, from the maintenance point of view it could simplify the situation if all the data is in one place. Of course if the amount of data grows huge over time you may have to separate it but in the beginning it would make sense to start small.

Database management

Otekpo Emmanuel19-Jul-15 9:38

Otekpo Emmanuel

19-Jul-15 9:38