SQL Linq, better idea than a join for as enumerable
-
So I have this SQL Linq statement, pretty easy until I added Free Shipping. In the original statement, I get the data from ORDER_COMPLETED and do a join to SHIPPING_REALTIME in which I grab the Realtime rate name. But Now, I added Free Shipping, by using another table that holds my criteria. So now I have 2 tables to get the rate name from, the other called SHIPPING_FREEMATRIX I'm trying to think of a better more proper way to do this. So what I have so far is
pResults = _
(
From oc In context.Order_Completed
Join srt In context.Shipping_Realtime On srt.RateAPICode Equals oc.RateAPICode // My Join
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "ORDER_CANCELED" _
And oc.OrderDate >= DbFunctions.AddDays(Today, -7)
Order By oc.OrderNumber
Select New coOrderIndex With
{
.OrderID = oc.CompletedOrderID,
.OrderDate = oc.OrderDate,
.OrderNumber = oc.OrderNumber,
.OrderStatus = oc.OrderStatus,
.LoginID = oc.LoginID,
.GrandTotal = oc.GrandTotal,
.RateAPICode = oc.RateAPICode,
.RateCharge = oc.TotalNetCharge,
.RateAPIName = If(srt.RateName Is Nothing, "", srt.RateName) // This is the rate name
}
).AsEnumerable()
pValue = pResults.Count()I tried using a tenary operator on RateName, but got an error saying First or Single can only be used at the end of the statement.
.RateAPIName = If(//Context.Any, //Single Linq Statement, //Single Linq Statement)
I tried a function but got the error can't execute an external function within a store statement
.RateAPIName = get_rateAPIName(oc.RateAPICode)
I tried a double join but got nothing
Join srt In context.Shipping_Realtime On srt.RateAPICode Equals oc.RateAPICode
Join fsm In context.Shipping_FreeMatrix On fsm.RateAPICode Equals oc.RateAPICodeI thought about taking the pResults and just looping back through using for each and populating the RateAPI Name, but I tried the above methods instead and aborted this thought.
for each pResult as coOrderIndex in pResults
pResult.RateAPIName = // Another statement
nextIf it was a single record that was easy for me, but doing it with multiple records is, well I'm not sure how to proceed.
-
So I have this SQL Linq statement, pretty easy until I added Free Shipping. In the original statement, I get the data from ORDER_COMPLETED and do a join to SHIPPING_REALTIME in which I grab the Realtime rate name. But Now, I added Free Shipping, by using another table that holds my criteria. So now I have 2 tables to get the rate name from, the other called SHIPPING_FREEMATRIX I'm trying to think of a better more proper way to do this. So what I have so far is
pResults = _
(
From oc In context.Order_Completed
Join srt In context.Shipping_Realtime On srt.RateAPICode Equals oc.RateAPICode // My Join
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "ORDER_CANCELED" _
And oc.OrderDate >= DbFunctions.AddDays(Today, -7)
Order By oc.OrderNumber
Select New coOrderIndex With
{
.OrderID = oc.CompletedOrderID,
.OrderDate = oc.OrderDate,
.OrderNumber = oc.OrderNumber,
.OrderStatus = oc.OrderStatus,
.LoginID = oc.LoginID,
.GrandTotal = oc.GrandTotal,
.RateAPICode = oc.RateAPICode,
.RateCharge = oc.TotalNetCharge,
.RateAPIName = If(srt.RateName Is Nothing, "", srt.RateName) // This is the rate name
}
).AsEnumerable()
pValue = pResults.Count()I tried using a tenary operator on RateName, but got an error saying First or Single can only be used at the end of the statement.
.RateAPIName = If(//Context.Any, //Single Linq Statement, //Single Linq Statement)
I tried a function but got the error can't execute an external function within a store statement
.RateAPIName = get_rateAPIName(oc.RateAPICode)
I tried a double join but got nothing
Join srt In context.Shipping_Realtime On srt.RateAPICode Equals oc.RateAPICode
Join fsm In context.Shipping_FreeMatrix On fsm.RateAPICode Equals oc.RateAPICodeI thought about taking the pResults and just looping back through using for each and populating the RateAPI Name, but I tried the above methods instead and aborted this thought.
for each pResult as coOrderIndex in pResults
pResult.RateAPIName = // Another statement
nextIf it was a single record that was easy for me, but doing it with multiple records is, well I'm not sure how to proceed.
I assume the SHIPPING_FREEMATRIX is not having corresponding rows for all rows in ORDER_COMPLETED. If that's the case you'll need to do an outer join. Here's[^] how to do that in query syntax.
Wrong is evil and must be defeated. - Jeff Ello
-
I assume the SHIPPING_FREEMATRIX is not having corresponding rows for all rows in ORDER_COMPLETED. If that's the case you'll need to do an outer join. Here's[^] how to do that in query syntax.
Wrong is evil and must be defeated. - Jeff Ello
That was hard to do. I got confused on whether to do a Join and Outer Join, so I had to experiment with it. But I got it to work in LinqPad4
Dim orders = _
(
From oc In context.Order_Completed
Group Join srs In context.Shipping_Realtime On oc.RateAPICode Equals srs.RateAPICode Into rRates = Group From srs In rRates.DefaultIfEmpty()
Group Join fsm In Context.Shipping_Free_Matrix On oc.RateAPICode Equals fsm.RateAPICode Into fRates = Group From fsm In fRates.DefaultIfEmpty()
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "ORDER_CANCELED" _
And oc.OrderDate >= DbFunctions.AddDays(Today, -7)
Order By oc.OrderNumber
Select New With
{
.OrderID = oc.CompletedOrderID,
.OrderDate = oc.OrderDate,
.OrderNumber = oc.OrderNumber,
.OrderStatus = oc.OrderStatus,
.LoginID = oc.LoginID,
.GrandTotal = oc.GrandTotal,
.RateAPICode = oc.RateAPICode,
.RateCharge = oc.TotalNetCharge,
.RateAPIName = If(Not srs.RateName is Nothing, srs.RateName, fsm.RateName)
}
).AsEnumerable()Thanks for the point in the right direction! I didn't know what to search for.
-
That was hard to do. I got confused on whether to do a Join and Outer Join, so I had to experiment with it. But I got it to work in LinqPad4
Dim orders = _
(
From oc In context.Order_Completed
Group Join srs In context.Shipping_Realtime On oc.RateAPICode Equals srs.RateAPICode Into rRates = Group From srs In rRates.DefaultIfEmpty()
Group Join fsm In Context.Shipping_Free_Matrix On oc.RateAPICode Equals fsm.RateAPICode Into fRates = Group From fsm In fRates.DefaultIfEmpty()
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "ORDER_CANCELED" _
And oc.OrderDate >= DbFunctions.AddDays(Today, -7)
Order By oc.OrderNumber
Select New With
{
.OrderID = oc.CompletedOrderID,
.OrderDate = oc.OrderDate,
.OrderNumber = oc.OrderNumber,
.OrderStatus = oc.OrderStatus,
.LoginID = oc.LoginID,
.GrandTotal = oc.GrandTotal,
.RateAPICode = oc.RateAPICode,
.RateCharge = oc.TotalNetCharge,
.RateAPIName = If(Not srs.RateName is Nothing, srs.RateName, fsm.RateName)
}
).AsEnumerable()Thanks for the point in the right direction! I didn't know what to search for.
Personally I prefer Method syntax[^] to Query syntax as the similarity to SQL confuses me rather than helps me.
Wrong is evil and must be defeated. - Jeff Ello
-
Personally I prefer Method syntax[^] to Query syntax as the similarity to SQL confuses me rather than helps me.
Wrong is evil and must be defeated. - Jeff Ello
I've been writing more method syntax in my new MVC projects in c#. I guess I was trying to just get this to work, and didn't want to totally rewrite it. This isn't a new project, just an expansion of my web forms based eCommerce program that I converted to Entity Framework over the summer, and flattened out the tables like you suggested last year, a total database overhaul. But for whats it worth, your advice has finally paid off for me, I haven't a complaint in over 30 days now. I'm stilling getting the hang of SQL Linq, and my Data Access Layer. All I have left now is to convert my web forms based app to a responsive design using the twitter bootstrap, and go back to the DAL for automatic database creation. If I haven't thanked you yet for the help and advice you gave me, then thank you very much. And yes I did implement it all.
-
I've been writing more method syntax in my new MVC projects in c#. I guess I was trying to just get this to work, and didn't want to totally rewrite it. This isn't a new project, just an expansion of my web forms based eCommerce program that I converted to Entity Framework over the summer, and flattened out the tables like you suggested last year, a total database overhaul. But for whats it worth, your advice has finally paid off for me, I haven't a complaint in over 30 days now. I'm stilling getting the hang of SQL Linq, and my Data Access Layer. All I have left now is to convert my web forms based app to a responsive design using the twitter bootstrap, and go back to the DAL for automatic database creation. If I haven't thanked you yet for the help and advice you gave me, then thank you very much. And yes I did implement it all.
:-O
Wrong is evil and must be defeated. - Jeff Ello