|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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()
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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???
|
|
|
|
|
I've seen those, never bothered to click on them.
Thanks
Guess you need to delete the message to keep it secret secret!
|
|
|
|
|
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?
|
|
|
|
|
While possible, I expect that any of those options will prove equally challenging to you.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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, shoulf
|
|
|
|
|
Where did your question go? 
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I'm using a postgresql database and managing it becomes harder and harder due to the sheer size of it.
the largest table contains about 2.5-3 billion records and there are some tables reaching about 10 million records. Database size is about 1.2 TB on disk. I believe the large table (and the tables around it) is the most problematic since there are (very roughly) 10 million write statements a day.
I have had issues with the transaction id's overflowing. Luckily by now I know the cure, but I would like to avoid this issue (and any other issue!) if possible. Especially since the database is expected to grow even further. VACUUM takes like forever on that large table.
I did a search on managing large databases, but most results are for other databases. Only found a not so helpful presentation on slideshare.
If anyone has good (practical) tips about keeping such a large database into good shape, please let me know.
(I hope I explained things well enough)
|
|
|
|
|
With that amount of data, I take it you already know enough about indexing etc so let's not go to those.
Depending what problem you're solving the technique might be different. Based on your description I'd concentrate on partitioning the large table (or tables) first. Partitioning would help you to isolate the hot spot of the table to a significantly smaller amount of rows so that changes in indexes and on disk would affect a considerable smaller amount of data.
For more information have a look at http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html[^]
As a side note, I hate to say this but I would also consider changing the database product 
|
|
|
|
|
Oracle? I know there is a free Oracle version (and a free SQL-Server version), but I'm not sure they'll do the job. Paying for it is a no go here (has to be as cheap as possible), so I'm down with or MySQL or PostgreSQL and I'll take the latter any day  .
Indexing, I'm not really an expert, but the indexes are limited to 2 (excluding the implicit index on the primary key). So if you have tips there, please shoot.
For this application I'm the designer, architect, tester, developer and, you guessed it, DBA despite my limited knowledge of being a 'real' dba. (there are little or no people doing better at our company so no choice there)
I'll have a look at partitioning.
[EDIT]Ooh, and thanks !  [/EDIT]
|
|
|
|
|
V. wrote: I know there is a free Oracle version (and a free SQL-Server version), but I'm not sure they'll do the job
The free one, no. It wouldn't be sufficient because of the limitations for the database size etc. So you'd have to go for the paid one...
One interesting option could be https://mariadb.org/[^]. If you have time at some point, have a look.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
