Strange, Could of swore when I first wrote this, I was just getting 1 record.
Then I changed the return value to List from IEnumerable and starting getting 3 records back for each record.
But I doubt that had anything to do with it. I did raise the .Net level to 4.5.2.
Must be my And Statement.
I tried Distinct but It doesn't seen to be working, most likely because I have no clue how use it here.
I started writing in c# and MVC and don't write like this anymore.
Public Shared Function load_COH_ListBox_Info(
ByRef pResults As List(Of cohIndex)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
From oh In context.Order_History
Where oh.OrderDate >= DbFunctions.AddDays(Today, -40) _
oh.OrderStatus = "COMPLETED" _
Or oh.OrderStatus = "ORDER_CANCELED" _
Or oh.OrderStatus = "ORDER_CANCELED_REFUND_COMPLETE"
Order By oh.OrderNumber Descending
Select New cohIndex With
.orderID = oh.CompletedOrderID,
.orderNumber = oh.OrderNumber,
.orderDate = oh.OrderDate,
.orderStatus = oh.OrderStatus,
.accountName = oh.LoginID,
.total_NetCharge = oh.GrandTotal
pValue = pResults.Count()
I am surprised as to why you didn't consider using GroupBy function? That would be useful in grouping the objects by a property, so that only distinct ones are returned, since they will be grouped by their property in a similar manner, to what you want to expect from Distinct function call here. Something like this,
// Since you said C#from oh in context.Order_History
where oh.OrderDate ...
orderby oh.OrderNumber desc
group oh by oh.RequiredProperty in ohGrouped // RequireProperty is the property you want to use
Something like this will be useful, as it will group the records by the properties which you specify. Please have a look here, [c# - Group by in LINQ - Stack Overflow](https://stackoverflow.com/questions/7325278/group-by-in-linq)
There is a library, which contains a lot of added features to LINQ, you should also look into it as it might also help you out with a lot of good function calls, such as this DistinctBy (DistinctBy takes the properties by which you want to select the records), get it here, GitHub - morelinq/MoreLINQ: Extensions to LINQ to Objects
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
This isn't a question about how to detect and prune unused tables in a live database. I'm more interested in the reasons why it should be done at all.
Here's some context. In our development environment we maintain a production database from which a schema and upgrade script is generated. When a database is upgraded it will create or alter existing tables on a live system, but not remove any tables.
After a debate about how it could be done and whether it should be done, I was struck by my colleague's statement of 'a lot of effort and potential pain, for not much gain'. There is of course the obvious risk of removing a table that really is used and causing a live system to fail.
There didn't seem to be much debate on whether defunct tables should be removed from a live database on the internet.
Apart from those of us who are hopelessly OCD about these sort of things, are there any good reasons for running the risk of removing defunct tables from a live system?
We use procedure that move tables into a recyclebin schema based on naming and date criteria (e.g. no live tables will contain digits in their names, but tables created/copied in response to support tickets will have the ticket number appended to the table name).
After a specified period (two weeks in our case) tables containing digits that were created prior to thespecified date are moved to the recycle bin schema. If someone screams that their table has gone, we move it back in to the original schema.
After another specified period (again two weeks), any table that has been in the recyclebin schema longer than that will be dropped.
This is all done automatically using an Agent job which calls a standard stored procedure we created.
I'm an optoholic - my glass is always half full of vodka.