SQL Linq, getting 3 of each record.

jkirkerx

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()

    pResults =
    (
        From oh In context.Order\_History
        Where oh.OrderDate >= DbFunctions.AddDays(Today, -40) \_
        And
        (
            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
        }
    ).Distinct().OrderByDescending(Function(ob) ob.orderNumber).ToList()
    pValue = pResults.Count()

    Return pValue

End Function

If it ain't broke don't fix it

jkirkerx

The 3 records each are in my database, let me fix that first

If it ain't broke don't fix it

Afzaal Ahmad Zeeshan

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

...
select ...

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](https://github.com/morelinq/MoreLINQ)

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

Afzaal Ahmad Zeeshan

Instead of removing them, you can just group them by their ID. :laugh:

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~