Summarising existing data into newly created record

Question

I am experienced in SQL and less experienced in LINQ and OOP in general, the result being much frustration with LINQ, so please bear with me.

I'm using MVC / Entity Framework as per tags below.

I have two tables. One table called Header is bound to a grid. When I create a record to be inserted into this Header table, I need to look up some matching related Detail records and summarise and show them in the grid.

I'll limit this to the LINQ aspects for now.

For example I have these detail records:

Date         Segment    Location    Amount1   Amount2
2013-12-01   ABC        ZZ          12        2
2013-12-02   ABC        ZZ          50        3
2013-12-03   ABC        ZZ          2         4
2013-12-01   DEF        ZZ          7         5

and I create this header record in my grid:

DateFrom     DateTo        Segment    Location   DetailAmount1   DetailAmount2
2013-12-01   2013-12-07    ABC        ZZ         (      to be populated      )

DetailAmount1 should be 64, DetailAmount2 should be 9

So my view calls the Grid_Create action in the controller to get a a viewmodel back with required data (which should have nothing except my summarised detail values and a DB generated key)

This is my controller:

    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Grid_Create(
        [DataSourceRequest]DataSourceRequest request,
        [Bind(Prefix = "models")]IEnumerable<Header_ViewModel> objects,
        string Location, 
        int? Segment_ID, 
        DateTime? Start_Date, 
        DateTime? End_Date)
    {
        using (var MyDB = new DBEntities())
        {
            // Keep the inserted entitites here. 
            // Used to return the result later.
            var entities = new List<Header_Table>();

            if (ModelState.IsValid)
            {
                foreach (var obj in objects)
                {
                    // PART A: Summarise estimates out of Detail
                    var est =
                        (from e in MyDB.Detail_Table
                         where e.SRC_System == Location
                         where e.Segment_ID == Segment_ID
                         where e.Transaction_Date >= Start_Date
                         where e.Transaction_Date <= End_Date
                         group e by e.Segment_ID into e
                         select
                         new Header_ViewModel
                         {
                             Amount1 = e.Sum(x => x.Amount1),
                             Amount2 = e.Sum(x => x.Amount2),
                             Amount3 = e.Sum(x => x.Amount3),
                             Amount4 = e.Sum(x => x.Amount4),
                             Amount5 = e.Sum(x => x.Amount5),
                             Amount6 = e.Sum(x => x.Amount6)
                         })
                         .FirstOrDefault();

                    // PART B: If there are no estimates, generate a 0
                    if (est == null)
                    {
                        est = new Header_ViewModel
                         {
                             Amount1 = 0,
                             Amount2 = 0,
                             Amount3 = 0,
                             Amount4 = 0,
                             Amount5 = 0,
                             Amount6 = 0
                         };
                    }

                    // PART C: Create a new entity 
                    // and set its properties from the posted model

                    var entity = new Header_Table
                    {
                        Transaction_ID = obj.Transaction_ID,
                        Value1 = obj.Value1,
                        Value2 = obj.Value2,
                        Value3 = obj.Value3,
                        // Summary from detail table
                        Amount2 = est.Amount2,//   obj.Amount2,
                        Amount3 = est.Amount3, // obj.Amount3,
                        Amount1 = est.Amount1,// obj.Amount1,
                        Amount4 = est.Amount4, //obj.Amount4,
                        Amount5 = est.Amount5, // obj.Amount5,
                        Amount6 = est.Amount6, //  obj.Amount6,
                        Location = Location,
                        Segment_ID = Segment_ID,
                        Start_Date = Start_Date,
                        End_Date = End_Date,
                        // assign default values
                        Updated_By = User.Identity.Name,
                        Updated_Date = DateTime.Now
                    };

                    // Add the entity
                    MyDB.Header_Table.Add(entity);
                    // Store the entity for later use
                    entities.Add(entity);
                }
                // Insert the entities in the database
                MyDB.SaveChanges();
            }
            // Return the inserted entities. Also return any validation errors.
            return Json(
                  entities.ToDataSourceResult(
                          request, 
                          ModelState, obj => new Header_ViewModel
            {
                Transaction_ID = obj.Transaction_ID,
                Amount2 = obj.Amount2,
                Value1 = obj.Value1,
                Amount3 = obj.Amount3,
                Amount1 = obj.Amount1
            }));
        }
    }

The questions:

1. There aren't always detail records to be found. Whats a nice way to default the est object to a single item of zero? Currently in part B I am checking for est==null and loading it up manually. How do I make FirstOrDefault do this for me automatically? (therefore removing part B). I believe I should be able to pass in a type but I can't get the syntax correct, i.e. if I grab everything from new in part B and put it as an argument to FirstOrDefault I get System.Linq.IQueryable<Header_ViewModel> does not contain a definition for 'FirstOrDefault...

2. When getting the summary in part A, I don't actually need to group by Segment_ID, I just need the total summary for the table. However it appears I have to group by something to get an aggregate in LINQ. I've seen other posts mentioning group by e.GetType() but I get the error LINQ to Entities does not recognize the method 'System.Type GetType()'......

3. Given that this is a new record, should I be able to populate entity directly out of MyDB.Detail_Table (thereby combining part A and C)? I did try this but got an error every time. Apologies for not posting the exact error, but if someone thinks it's possible I will try again and post errors this time.

Answer 1

Question 1 The easiest solution I can think of is this (using your code as a base):

var est =
  (from e in MyDB.Detail_Table
   where e.SRC_System == Location
   where e.Segment_ID == Segment_ID
   where e.Transaction_Date >= Start_Date
   where e.Transaction_Date <= End_Date
   group e by e.Segment_ID into e
   select
   new Header_ViewModel
   {
       Amount1 = e.Sum(x => x.Amount1),
       Amount2 = e.Sum(x => x.Amount2),
       Amount3 = e.Sum(x => x.Amount3),
       Amount4 = e.Sum(x => x.Amount4),
       Amount5 = e.Sum(x => x.Amount5),
       Amount6 = e.Sum(x => x.Amount6)
   })
   .FirstOrDefault() ?? new Header_ViewModel();

Question 2 Well, yeah... Not much can be done here, using a constant is possible as explained here. This seems to work:

var est =
    MyDB.DetailTableSet.Where(e => e.SRC_System == Location && e.Segment_ID == Segment_ID 
        && e.Transaction_Date >= Start_Date && e.Transaction_Date <= End_Date)
        .GroupBy(e => 1)
        .Select(e => new Header_ViewModel
        {
            Amount1 = e.Sum(x => x.Amount1),
            Amount2 = e.Sum(x => x.Amount2),
        })
        .SingleOrDefault() ?? new Header_ViewModel();

Question 3 There is no good way to do this. The reason and some work around are explained here, but in your case I would not go that far. This is as far as I would go:

var est =
    MyDB.DetailTableSet.Where(e => e.SRC_System == Location && e.Segment_ID == Segment_ID 
        && e.Transaction_Date >= Start_Date && e.Transaction_Date <= End_Date)
        .GroupBy(e => 1)
        .Select(e => new Header_ViewModel
        {
            Amount1 = e.Sum(x => x.Amount1),
            Amount2 = e.Sum(x => x.Amount2),
        })
        .SingleOrDefault() ?? new Header_ViewModel();

var entity = new HeaderTable()
{
    Transaction_ID = obj.Transaction_ID,
    Value1 = obj.Value1,
    Value2 = obj.Value2,
    Value3 = obj.Value3,
    // Summary from detail table
    Amount1 = est.Amount1,
    Amount2 = est.Amount2,
    Location = Location,
    Segment_ID = Segment_ID,
    Start_Date = Start_Date,
    End_Date = End_Date,
    // assign default values
    Updated_By = "Myself",
    Updated_Date = DateTime.Now
};


// Add the entity
MyDB.HeaderTableSet.Add(entity);
// Store the entity for later use
entities.Add(entity);