Concat question

[agolovan]

This is my code:

var rows = extractedRows.AsQueryable();

var detailed = rows
.GroupBy("new (Region, Product)")
.Select("new (Key.Region as Region, Key.Product as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 0 as GroupLevel)");

// GROUPING SET 2: (Region)
var regionSubtotal = rows
	.GroupBy("Region")
	.Select("new (Key as Region, null as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 1 as GroupLevel)");

How could I use Concat for detailed and regionSubtotal and order by Product the result using DynamicLINQ?

This code does not work:

var combined = detailed.Concat(regionSubtotal).AsQueryable();

This code works but I could not use .OrderBy("Product").

var combined = detailed.ToDynamicList().Concat(detailed1.ToDynamicList()).AsQueryable();

I need to calculate sorting expression using some metadata, so be able to have a string for sorting is a great help.

But I actually need to do in a code something like that:

CREATE TABLE SalesAlex (
    Region VARCHAR(20),
    Product VARCHAR(20),
    Sales INT
);


INSERT INTO SalesAlex (Region, Product, Sales) VALUES
('North', 'Apples', 100),
('North', 'Oranges', 150),
('South', 'Apples', 200),
('South', 'Oranges', 250);

SELECT 
    Region,
    Product,
    SUM(Sales) AS TotalSales,
    GROUPING_ID(Region, Product) AS GroupLevel
FROM SalesAlex
GROUP BY GROUPING SETS (
    (Region, Product),  -- Detailed totals
    (Region),           -- Subtotals by Region
    ()                  -- Grand total
)

I made that working with Dynamic LINQ, but also need to sort the result by Product for example.

Any ideas really help!

[StefH]

@agolovan
To get this working [with the current version], you need to create a class which defines the grouped result:

public class GroupedSalesData
{
    public string Region { get; set; }
    public string? Product { get; set; }
    public int TotalSales { get; set; }

    public int GroupLevel { get; set; }
}

And use .Select<GroupedSalesData>.

Example:

var extractedRows = new List<SalesData>
{
    new() { Region = "North", Product = "Widget", Sales = "100" },
    new() { Region = "North", Product = "Gadget", Sales = "150" },
    new() { Region = "South", Product = "Widget", Sales = "200" },
    new() { Region = "South", Product = "Gadget", Sales = "100" },
    new() { Region = "North", Product = "Widget", Sales = "50" }
};

var rows = extractedRows.AsQueryable();

// GROUPING SET 1: (Region, Product)
var detailed = rows
    .GroupBy("new (Region, Product)")
    .Select<GroupedSalesData>("new (Key.Region as Region, Key.Product as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 0 as GroupLevel)");

// GROUPING SET 2: (Region)
var regionSubtotal = rows
    .GroupBy("Region")
    .Select<GroupedSalesData>("new (Key as Region, null as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 1 as GroupLevel)");

var combined = detailed.Concat(regionSubtotal);
var ordered = combined.OrderBy("Product").ToDynamicList();

[StefH]

#907

[agolovan]

This approach works really well - THANK YOU VERY MUCH -this is what I have now that is working with SAMPLE data table:

public class GroupedSalesData
{
	public string Region { get; set; }
	public string? Product { get; set; }
	public int TotalSales { get; set; }
	public int GroupLevel { get; set; }
}

public class SalesData
{
	public string Region { get; set; } = string.Empty;
	public string Product { get; set; } = string.Empty;
	public int Sales { get; set; }
}

var eInfoJoinTable = new DataTable();
eInfoJoinTable.Columns.Add("Region", typeof(string));
eInfoJoinTable.Columns.Add("Product", typeof(string));
eInfoJoinTable.Columns.Add("Sales", typeof(int));

eInfoJoinTable.Rows.Add("North", "Apples", 100);
eInfoJoinTable.Rows.Add("North", "Oranges", 150);
eInfoJoinTable.Rows.Add("South", "Apples", 200);
eInfoJoinTable.Rows.Add("South", "Oranges", 250);

var extractedRows = eInfoJoinTable.AsEnumerable()
	.Select(row => new SalesData
	{
		Region = row.Field<string>("Region") ?? String.Empty,
		Product = row.Field<string>("Product") ?? String.Empty,
		Sales = row.Field<int?>("Sales") ?? 0
	})
	.ToList();

var rows = extractedRows.AsQueryable();

// GROUPING SET 1: (Region, Product)
var detailed = rows
	.GroupBy("new (Region, Product)")
	.Select<GroupedSalesData>("new (Key.Region as Region, Key.Product as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 0 as GroupLevel)");

// GROUPING SET 2: (Region)
var regionSubtotal = rows
	.GroupBy("Region")
	.Select<GroupedSalesData>("new (Key as Region, null as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 1 as GroupLevel)");

// GROUPING SET 3: Grand Total (single row)
var grandTotal = rows
	.Select("new (Convert.ToInt32(Sales) as Sales)")
	.GroupBy("true") 
	.Select<GroupedSalesData>("new (null as Region, null as Product, Sum(Sales) as TotalSales, 3 as GroupLevel)");

var combined = detailed.Concat(regionSubtotal).Concat(grandTotal);
var ordered = combined.OrderBy("Product desc").ToDynamicList();

The only issue is that I’ll be dynamically generating all the strings for grouping, ordering, etc. based on metadata. That’s why using Dynamic LINQ is a great approach. However, I don’t really know anything about the GroupedSalesData or SalesData classes — it seems to me it would be great if we could do same operations dynamically, without needing of this classes. :)

I see PR that eliminates need for both GroupedSalesData and SalesDataas I understand it.
#913

Thanks a lot for your help!

[agolovan]

I see you Q912 test.

 var extractedRows = new List<SalesData>
        {
            new() { Region = "North", Product = "Widget", Sales = "100" },
            new() { Region = "North", Product = "Gadget", Sales = "150" },
            new() { Region = "South", Product = "Widget", Sales = "200" },
            new() { Region = "South", Product = "Gadget", Sales = "100" },
            new() { Region = "North", Product = "Widget", Sales = "50" }
        };

        var rows = extractedRows.AsQueryable();

        // GROUPING SET 1: (Region, Product)
        var detailed = rows
            .GroupBy("new (Region, Product)")
            .Select("new (Key.Region as Region, Key.Product as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 0 as GroupLevel)");

        // GROUPING SET 2: (Region)
        var regionSubtotal = rows
            .GroupBy("Region")
            .Select("new (Key as Region, null as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 1 as GroupLevel)");

        var combined = detailed.ToDynamicArray().Concat(regionSubtotal.ToDynamicArray()).AsQueryable();
        var ordered = combined.OrderBy("Product").ToDynamicList();

Could you add another test please?

var eInfoJoinTable = new DataTable();
eInfoJoinTable.Columns.Add("Region", typeof(string));
eInfoJoinTable.Columns.Add("Product", typeof(string));
eInfoJoinTable.Columns.Add("Sales", typeof(int));

eInfoJoinTable.Rows.Add("North", "Apples", 100);
eInfoJoinTable.Rows.Add("North", "Oranges", 150);
eInfoJoinTable.Rows.Add("South", "Apples", 200);
eInfoJoinTable.Rows.Add("South", "Oranges", 250);

var extractedRows =
	from row in eInfoJoinTable.AsEnumerable()
	select row;

var rows = extractedRows.AsQueryable();

// GROUPING SET 1: (Region, Product)
var detailed = rows
.GroupBy("new (Region, Product)")
.Select("new (Key.Region as Region, Key.Product as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 0 as GroupLevel)");

// GROUPING SET 2: (Region)
var regionSubtotal = rows
	.GroupBy("Region")
	.Select("new (Key as Region, null as Product, Sum(Convert.ToInt32(Sales)) as TotalSales, 1 as GroupLevel)");

var combined = detailed.ToDynamicArray().Concat(regionSubtotal.ToDynamicArray()).AsQueryable();
var ordered = combined.OrderBy("Product").ToDynamicList();

[StefH]

@agolovan
I did add another test using DataTable, and with the new changes in the code, it seems to work fine.