Entity Framework Core with .NET Core 1.1

[Devyn Cunningham]

I am currently working on a side project in C# using Entity Framework core and have run into a bit of a road block.  I was hoping someone could walk me through specific details of Computed Columns vs Not Mapped vs DatabaseGenerated.  I am attempting to use a server side computed column in a view model to sum all values of its navigational reference values.  Think [Account] And [Transactions] with [Account] having a navigational references to all transactions associated with it.  Then a calculated column that attempt to get the sum of all its transactions.  

[Bill Israel]

If no one has any experience with C# and/or Entity Framework, do you know someone that might? This might be a blind spot or gap in the group we're attempting to assemble here and it'd be nice to have someone to reach out to that might be able to fill it. Thanks!

[Devyn Cunningham]

I have a C# guru outside of Penny University that helped me with my issue.  I will recommend him to be a mentor.  

The solution had to do with preloading the data from the adjoined tables using the EF function .Include.  The function needed to be called before the filter to ensure the tables were loaded before the column was ever read. 

[Mike Schuld]

This issue is pretty common with .NET ORM frameworks, because a lot of them rely on the fact that LINQ in .NET (basically queries written in C# syntax in code instead of in SQL) are interpreted / executed on an as needed basis and deferred until the data they work with fully needed.

When calling Queryable LINQ results from other code, any new LINQ statements being called are just tacked on to the deferred statements until there is some kind of final call to get the data (like Count(), Sum(), ToList(), etc.) When that call happens, if there is even one piece of code inside one of the LINQ statements that the SQL query parser can't figure out how to turn into SQL, the whole statement fails to parse. Here's a more concrete example:

DateTime after = new DateTime(2016,1,1);

decimal total = DatabaseSession.Query<DataType>()
    .Where(item => item.Created >= after)
    .Where(item => item.Status == Status.Active)
    .Where(item => item.Components.Sum(component => component.Price) < 1000) // Sum here may not parse happily as it would require a separate join in the DB
    .Sum(item => item.Component.Sum(component => component.Price);
...

decimal total = DatabaseSession.Query<DataType>()
    .Where(item => item.Created >= after)
    .Where(item => item.Status == Status.Active)
    .ToList() // This forces the parsing and pulling of the data
    .Where(item => item.Components.Sum(component => component.Price) < 1000) // Data already pulled, so no parsing to SQL needed now
    .Sum(item => item.Component.Sum(component => component.Price);

Some ORMs actually have a separate query building library that helps to hint at what you mean for certain situations like this to work around the shortcomings of the parser. They may also have an extensible framework in place to let you define exactly what something like a Sum inside a query should do.