How would one create summary columns in query results using Linq-to-SQL?

linq group by sum multiple columns
linq group by select
linq query for sum of column
linq sum
group by and sum in list c#
linq average group by
select sum in linq query
linq groupby sum c

Let's say we have these tables:

CAR
ID  Name
1   Mustang
2   Taurus

CAR_PART
ID   CAR_ID   PART_NUMBER
1    1        M772
2    1        A443
3    2        Z889

CAR_COLOR
ID     CAR_ID  COLOR_NAME
1      1       Red
2      1       Blue
3      2       Yellow

We need to use Linq-to-SQL to get this result:

CAR_ID   CAR_NAME   CAR_PART_LIST     CAR_COLOR_LIST
1        Mustang    M772,A443         Red,Blue

How would this be accomplished? I have a new class created with the result column names, and figure a select new MyClass{}; at the end would be good, but am not sure how to handle the multiple groupings for the CAR_PART_LIST and CAR_COLOR_LIST columns.

Any ideas?


edit: here is what I have so far:

from car in db.CAR
join part in db.CAR_PART on car.ID equals part.CAR_ID
join color in db.CAR_COLOR on car.ID equals color.CAR_ID
where car.ID = 1
select new MySearchResult{
     CAR_ID = car.ID,
     CAR_NAME = car.Name,
     CAR_PART_LIST = ?,
     CAR_COLOR_LIST = ?
}

public class MySearchResult{
    public int CAR_ID { get; set; }
    public string CAR_NAME { get; set; }
    public string CAR_PART_LIST { get; set; }
    public string CAR_COLOR_LIST { get; set; }

    public MySearchResult() { }
}

Using the obvious String extension method:

public static string Join(this IEnumerable<string> s, string sep) => String.Join(s, sep);

You can compute the answer by using group join on each related table:

var ans = from car in db.CAR
          join part in db.CAR_PART on car.ID equals part.CAR_ID into partj
          join color in db.CAR_COLOR on car.ID equals color.CAR_ID into colorj
          where car.ID == 1
          select new MySearchResult {
              CAR_ID = car.ID,
              CAR_NAME = car.Name,
              CAR_PART_LIST = partj.Select(p => p.PART_NUMBER).Join(","),
              CAR_COLOR_LIST = colorj.Select(c => c.COLOR_NAME).Join(",")
          };

c#, You can extend type of tb entity by sum property and write something like: table1 .​Select(tb => new { Tb = tb, Sum = tb.col1 + tb.col2 }) .Select(x => { x.Tb.sum = x. In LINQ to SQL, the select query is used to get all the records or rows from the table and we can use LINQ to SQL select query to filter table records with where clause. We can perform multiple operations like grouping, joining, etc. using LINQ to SQL select query based on our requirements.

Do you have foreign keys set up for db.CAR_PART and db.CAR_COLOR? If so, that linq-to-sql will automatically give you properties for the joins. So, it becomes:

var q = from car in db.Car
where car.ID == 1
select new MySearchResult
{
    CAR_ID = car.ID,
    CAR_NAME = car.Name,
    CAR_PART_LIST = String.Join(",", car.CAR_PARTs.Select(cp=>cp.PART_NUMBER))
    CAR_PART_LIST = String.Join(",", car.CAR_COLORs.Select(cp=>cp.COLOR_NAME))
};

Grouping and Aggregating Data with LINQ, This gives us results like this: 1 2 The group by statement gives us this same capability in LINQ – by grouping our objects, we The first step in creating any query is to refine the fields we want to return – if the quality of our If you're familiar with T-SQL you're already saying, "We need a HAVING clause". LINQ to SQL Provider − converts query to Transact SQL (T-SQL) and sends the new query to the ADO Provider for execution. ADO Provider − After execution of the query, send the results in the form of a DataReader to LINQ to SQL Provider which in turn converts it into a form of user object.

So you have a table of CarParts, where every CarPart has a CarId and a PartNumber; and you have a table of CarColours, where every CarColour has a Carid and a ColourName.

I assume you do not support invisible cars, so every car has at least one part, and one colour.

You want a sequence of all CarIds, each CarId with the CarName, a list of all CarParts belonging to this CarId(= that have this CarId as foreign key) and a list of all ColourNames belonging to this CarId (again using the foreign key.

To do this, first we get all CarIds with their CarParts and all CarIds with their ColourNames, then we can Join the results on common CarId.

If you think there might be cars without parts or without colours, you need to do a 'Full outer Join' instead of a normal Join. This is not part of standard LINQ, but you can write the extension function yourself. See LINQ Full Outer Join

After the join on common CarId, we Join the result with your Cars on CarId

var partsGroupedByCarId = carParts.GroupBy(     // make groups of carParts
    carPart => carPart.CarId);                  // with common CarId as Key

var coloursGroupedByCarId = carColours.GroupBy( // make groups of carColours
    carColour => carColour.CarId);,             // with common CarId as Key

var joinResult = partsGroupedByCarId.Join(      // Join the two groups
    coloursGroupedByCarId,
    partGroup => partGroup.Key,                 // from each group of parts take the key (= CarId)
    colourGroup =>                              // from each group of colours take the key (= CarId)
    (partGroup, colourGroup) => new             // when they match make a new object
    {
        CarId = partGroup.Key,                  // with the common CarId
        CarParts = partGroup                    // all partNumbers of the car with CarId
           .Select(item => item.PartNumber),
        CarColours = colourGroup                 // all colourNames of the car with carId
            .Select(item => item.ColourName),
    });

Finally a Join of the Cars with all their Colours and Parts:

var result = Cars.Join(joinResult,    // Join all cars with the joinResult
    car => Id,                        // from every car take the id
    joinedItem => joinedItem.CarId,   // from every joinedItem take the CarId
    (car, joinedItem) => new          // for every car with its matching joinedItem
    {                                 // make a new object
         Id = car.Id,
         Name = car.Name,
         Parts = joinedItem.CarParts.ToList(),
         Colours = joinedItem.CarColours.ToList(),             
    });

TODO: consider creating one big LINQ statements. As all statements use deferred execution I don't think this will improve efficiency. It certainly will decrease readability.

Grouping Results in LINQ -- Visual Studio Magazine, Complex queries can be solved with a combination of simpler LINQ queries, objects -- it's a summary value generated from a group of objects that share I walked, line by painful line, through the LINQ equivalent of a SQL outer join). To do that, in my query's select clause, I create an anonymous object  To add a project that contains a LINQ to SQL file. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template. Name the file northwind.dbml.

I writed it here dotnetfiddle please check it out:

    var q = from car in cars
    join part in carparts on car.ID equals part.CAR_ID  into parts
    join color in carcolors on car.ID equals color.CAR_ID into clrs
    where  car.ID == 1

 select new MySearchResult{
 CAR_ID = car.ID,
 CAR_NAME =  car.Name,
 CAR_PART_LIST =  String.Join(",",parts.Select(p => p.PART_NUMBER)),
 CAR_COLOR_LIST = String.Join(",",clrs.Select(c => c.COLOR_NAME))};

        foreach (var item in q)
            Console.WriteLine("{0} {1} {2} {3}",
                item.CAR_ID,
                item.CAR_NAME,
                item.CAR_PART_LIST,
                item.CAR_COLOR_LIST);

How to: Count, Sum, or Average Data by Using LINQ, The sample counts, sums, and averages the results by using the Aggregate and Group By clauses. Select the LINQ to SQL Classes item template. You can create an instance of the DataContext in your code and query the  Using the LINQ to SQL designer I can easily create a representation of the sample "Northwind" database like below: My LINQ to SQL design-surface above defines four entity classes: Product, Category, Order and OrderDetail. The properties of each class map to the columns of a corresponding table in the database.

Aggregate Functions in Linq To SQL, AVERAGE() : Returns the average of column values. COUNT() I create a data context class to perform aggregate function operations. 1. In SQL Server SELECT SUM(SALARY) AS [TOTAL SALARY] FROM EMPLOYEE. Let’s go to the next one. Goal 3: Add columns for top sold Product and participation of it. Our next step is to extract the top product and also its amount on a row by row basis from the column Products which is this case is a table. We can do so by using a new custom column with the following Table.Max function:

LINQ to SQL: Querying Data, NET Framework 4 covers LINQ to SQL features used to query data. Using ToList or ToArray, you can convert the results of a query into a List or an Chapter 14 describes the techniques to dynamically build a query tree in a If you specify a type with more public members than available columns, the  LINQ to SQL translates the queries you write into equivalent SQL queries and sends them to the server for processing. More specifically, your application uses the LINQ to SQL API to request query execution. The LINQ to SQL provider then transforms the query into SQL text and delegates execution to the ADO provider.

4. Exploring LINQ to Entities in Greater Depth, It is also possible for Object Services queries to return rows and columns. You'll The queries you build here will be demonstrated using Entity SQL in Chapter 5. With LINQ or Entity SQL queries you can shape your results by picking Summary. In this chapter, you have learned a variety of ways to use LINQ to Entities to  Let us learn it practically how to convert LINQ query result to Datatable by creating one simple application as Now create the project as: "Start" - "All Programs" - "Microsoft Visual Studio 2010". "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).

Comments
  • Actually, it turns out group join is sufficient.
  • Where would one put this extension method? (in the Controller, or elsewhere?)
  • You can always just replace the extension method with a call to String.Join.
  • Unfortunately, there are no foreign keys (it's a long story, but just not going to be possible). I wonder if there is a way to do this without foreign keys?