C#•2y ago

LInq Combine duplicates

Hey everyone, trying to figure out how to combine my dates together so that it doesn't separate the data. This is what I am currently working with

    Stores
        .Select(x => new
        {
            City = x.City,
            Location = x.Location,
            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })
        }).Dump();

    Stores
        .Select(x => new
        {
            City = x.City,
            Location = x.Location,
            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })
        }).Dump();

116 Replies

YashogiOP•2y ago

Basically need my Date section to not repeat but rather combine them into one date

Henkypenky•2y ago

GroupBy

YashogiOP•2y ago

Yeah I figured but I'm not sure what to group with here

Henkypenky•2y ago

Date as you said

YashogiOP•2y ago

Where should I place my groupby? I was thinking after my where clause But would it make more sense to do it earlier

Henkypenky•2y ago

do you know sql

YashogiOP•2y ago

Got a general understanding

Henkypenky•2y ago

think about the order what do you do first

YashogiOP•2y ago

In the beginning Is what I would think after I call orders

Henkypenky•2y ago

first statement

YashogiOP•2y ago

Yeah Okay I'll give it a shot

Henkypenky•2y ago

let's say i want to: group all my invoices by date that are more than 15.000 usd what is the order in sql

YashogiOP•2y ago

I would like to believe you would groupby First and then a where?

Henkypenky•2y ago

so, if you groupby first, what do you groupby? since you don't have anything yet

YashogiOP•2y ago

Ohhh I should put it after my .where so I got access to the data

Henkypenky•2y ago

yeah so it goes something like this: get the data from somewhere, where some condition(s) apply, then group by some column, then order by some condition

YashogiOP•2y ago

    .GroupBy(o => o.OrderDate)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })
        }).Dump();

    .GroupBy(o => o.OrderDate)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })
        }).Dump();

Ahhhh okay Issue is when I place my groupby in that location I get errors in my select is that because of my orderby?

Henkypenky•2y ago

cause that's not where it goes you want to groupby sales not Store

YashogiOP•2y ago

Oh ok

    Sales = Orders.GroupBy(o => o.OrderDate).Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)

    Sales = Orders.GroupBy(o => o.OrderDate).Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)

Sorry Kinda new to this started last month I believe i'm understanding what you're getting at though

Henkypenky•2y ago

where goes first remember select where groupby orderby https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16#logical-processing-order-of-the-select-statement

SELECT ORDER
--------------
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

SELECT ORDER
--------------
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

YashogiOP•2y ago

Alright so this is the correct sequence I need my group by after my where

Henkypenky•2y ago

YashogiOP•2y ago

            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)

            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)

So I need to add it before the orderby but after the where

Henkypenky•2y ago

the GroupBy? yes

YashogiOP•2y ago

When I put it directly after my where clause my orderby gets an error

Henkypenky•2y ago

what's the error?

YashogiOP•2y ago

            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).GroupBy(o => o.OrderDate).OrderBy(o => o.OrderDate.Day)

            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).GroupBy(o => o.OrderDate).OrderBy(o => o.OrderDate.Day)

Does not contain definition for OrderDate

Henkypenky•2y ago

that's because it's not where it goes, again you are returning 3 things: 1) City 2) Location 3) Sales: here you need to go deep, so you select again then you groupby then you orderby does it make sense?

YashogiOP•2y ago

Trying to figure this out sorry if it's taking a while

Henkypenky•2y ago

no worries at all

YashogiOP•2y ago

So city and location is working as intended For sales I first linked the two tables using a where clause and I also added a month== 12 condition because I want it to only be dec You are saying I should select and then group by and order by?

Henkypenky•2y ago

this works?

Stores
        .Select(x => new
        {
            City = x.City,
            Location = x.Location,
            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })
        }).Dump();

Stores
        .Select(x => new
        {
            City = x.City,
            Location = x.Location,
            Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })
        }).Dump();

YashogiOP•2y ago

eah Yeah Yeah Wait Yeah that works I just need to insert a group by into here somewhere but I just really suck at figuring this out lol

Henkypenky•2y ago

so let's go again remove that orderby from there now that you selected the sales what do you do

YashogiOP•2y ago

Okay it is now gone I should group by then order by

Henkypenky•2y ago

where

YashogiOP•2y ago

After the select wait

Henkypenky•2y ago

which one

YashogiOP•2y ago

group by before select orderby after

Henkypenky•2y ago

no select starts everything

YashogiOP•2y ago

Alright sure So I should group by prior to my order by

Henkypenky•2y ago

you are selecting a store right? at the beginning

YashogiOP•2y ago

Yeah A store linking to orders

Henkypenky•2y ago

and then you are selecting sales right? .Select(bla)

YashogiOP•2y ago

Yeah

Henkypenky•2y ago

so where does the groupby and orderby go are you grouping sales or stores?

YashogiOP•2y ago

orders so sales

Henkypenky•2y ago

so..

YashogiOP•2y ago

I feel so dumb right now lemme give it a look So within my select statement I should be looking to insert a group and order by

Henkypenky•2y ago

you are not dumb you are joining tables sql sucks xd if you don't like this approach just let me know and i'll tell you where i think it should go

YashogiOP•2y ago

I like learning though I think I can learn from an example you give me I actually absorbed a lot from that earlier list you sent of the sql order

Henkypenky•2y ago

my example is, you select stores, then you select sales where does the groupby go and proceeding orderby

YashogiOP•2y ago

It should go after the select?

Henkypenky•2y ago

of what selection

YashogiOP•2y ago

date? OrderDate but obv my sales

Henkypenky•2y ago

yes so you selected the sales then you?

YashogiOP•2y ago

group by I just don't know where in my code

Henkypenky•2y ago

then you?

YashogiOP•2y ago

but after I group by I add orderby group first order second

Henkypenky•2y ago

show me where you select the sales

YashogiOP•2y ago

 Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })

 Sales = Orders.Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12)
            .Select (s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            })

the .select in the middle

Henkypenky•2y ago

so what comes after a select

YashogiOP•2y ago

Groupyby

Henkypenky•2y ago

okay does it make sense?

YashogiOP•2y ago

Yeah but like Do I put it after everything i've sleected like the gst product sales and stuff?

Henkypenky•2y ago

yes

YashogiOP•2y ago

at the bracket near the end? Yeah I get an error when I add a group by there

Henkypenky•2y ago

what's the error?

YashogiOP•2y ago

OHH Date I should use date not orderdate duuude I've put it there so many times but that was the error I got

Henkypenky•2y ago

need to pay more attention xd

YashogiOP•2y ago

so I assumed it was in the wrong place

Henkypenky•2y ago

just to clarify, a select can be cut short, so in this case you are selecting 2 columns from store right? then you want to join another table so you start another select and the ordering starts agian again i think it can be corrected a little bit though, not very good with EF

YashogiOP•2y ago

Alright but here is my issue my orderby gives an error

Henkypenky•2y ago

what error?

YashogiOP•2y ago

YashogiOP•2y ago

that date time date, subtotal and gst do not contain definition for orderdate

Henkypenky•2y ago

they dont, you didn't select it you selected Date so o.Date .whatever is Date DateTime?

YashogiOP•2y ago

it's orderdate which is a datetime in the database

Henkypenky•2y ago

so .OrderBy(o => o.Date.Day)

YashogiOP•2y ago

I had that and it didn't work and it didn't work but when I literally rewrote it the exact same way it worked I think it was just a visual bug

Henkypenky•2y ago

it can happen did u get the group and order now?

YashogiOP•2y ago

Oh wait when I click run it doesn't work using date error doesn't show up til after

Henkypenky•2y ago

in the groupby or orderby? and what's the error while compiling?

YashogiOP•2y ago

Cannot execute text selection: CS1061 'IGrouping<DateTime, <anonymous type: DateTime Date, decimal ProductSales, decimal GST>>' does not contain a definition for 'Date' and no accessible extension method 'Date' accepting a first argument of type 'IGrouping<DateTime, <anonymous type: DateTime Date, decimal ProductSales, decimal GST>>' could be found (press F4 to add an assembly reference or import a namespace)

Henkypenky•2y ago

can you show me Date

YashogiOP•2y ago

The database table?

Henkypenky•2y ago

model Sales sorry

YashogiOP•2y ago

Henkypenky•2y ago

can you do Orders.Select( ).Where().GroupBy().OrderBy(); ?

YashogiOP•2y ago

I'll give it a shot

Henkypenky•2y ago

Stores
        .Select(x => new
        {
            City = x.City,
            Location = x.Location,
            Sales = Orders.Select(s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            }).Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day).GroupBy().OrderBy()
        }).Dump();

Stores
        .Select(x => new
        {
            City = x.City,
            Location = x.Location,
            Sales = Orders.Select(s => new 
            {
                Date = s.OrderDate,
                ProductSales = s.SubTotal,
                GST = s.GST
            }).Where(o => x.StoreID == o.StoreID && o.OrderDate.Month == 12).OrderBy(o => o.OrderDate.Day).GroupBy().OrderBy()
        }).Dump();

YashogiOP•2y ago

My where starts to get an error that way the store id

Henkypenky•2y ago

yeah

YashogiOP•2y ago

and orderdate.month

Henkypenky•2y ago

lost context

YashogiOP•2y ago

How would I go about fixing it so I link the tables and get the same condition

Henkypenky•2y ago

thinking

YashogiOP•2y ago

Never mind I think I found another solution I've decided to re work some of it

Henkypenky•2y ago

oh nice

YashogiOP•2y ago

    .OrderBy(s => s.City)
    .ThenBy(s => s.Location)
    .Select(s => new 
    {
        City = s.City,
        Location = s.Location,
        Sales = Orders
            .Where(dateGroup => s.StoreID == dateGroup.StoreID && dateGroup.OrderDate.Month == 12)
            .GroupBy(o => new {o.OrderDate})
            .Select(s => new 
            {
                Sales = s.Key.OrderDate,
                NumberOfOrders = s.Count(),
            })

    })
    .Dump();

    .OrderBy(s => s.City)
    .ThenBy(s => s.Location)
    .Select(s => new 
    {
        City = s.City,
        Location = s.Location,
        Sales = Orders
            .Where(dateGroup => s.StoreID == dateGroup.StoreID && dateGroup.OrderDate.Month == 12)
            .GroupBy(o => new {o.OrderDate})
            .Select(s => new 
            {
                Sales = s.Key.OrderDate,
                NumberOfOrders = s.Count(),
            })

    })
    .Dump();

My issue now is that I can't access gst and subtotal

Henkypenky•2y ago

yeah cause they are in sales

cptcrawler•2y ago

can you try with a SelectMany() ?

YashogiOP•2y ago

Not sure if I I was taught to use that yet

Henkypenky•2y ago

maybe a join can ease things up:

cptcrawler•2y ago

like yourquery().SelectMany(x => x.Sales.Sales, (x,y ) => // somethinghere);

YashogiOP•2y ago

Yeah I don't think I can do that Doing some take home work

cptcrawler•2y ago

Why ? oh

YashogiOP•2y ago

and i'm only allowed to use what I know or was taught that might be something I learn at a later dae

cptcrawler•2y ago

uhm that's going to be though

YashogiOP•2y ago

Yeahhh

cptcrawler•2y ago

give me a sec

YashogiOP•2y ago

This question is crazy hahaha

cptcrawler•2y ago

going to have a look at your first question So you are just actually trying to compact your dates as a PARTITION BY does kinda

Henkypenky•2y ago

try:

var query =
        stores.Join(
            orders,
            order => order.StoreID,
            store => store.StoreID,
            (store, order) => new
            {
                City = store.City,
                Location = store.Location,
                Date = order.OrderDate,
                ProductSales = order.SubTotal,
                GST = order.GST
            }).GroupBy().OrderBy();

var query =
        stores.Join(
            orders,
            order => order.StoreID,
            store => store.StoreID,
            (store, order) => new
            {
                City = store.City,
                Location = store.Location,
                Date = order.OrderDate,
                ProductSales = order.SubTotal,
                GST = order.GST
            }).GroupBy().OrderBy();

get your context for orders and stores

YashogiOP•2y ago

Yeah I gave that a shot no can do, I appreciate the help guys I've learnt a lot but I think I gotta wrack my brain at it

cptcrawler•2y ago

You can actually have an autoproperty inside the stores and doing then a .Include()

Gaming

Programming

LInq Combine duplicates