locked
Need help with MVC inheritance and Linq RRS feed

  • Question

  • User1901201124 posted

    Hi All, 

    I have a slight issue with my Linq query. I thought I had gotten to work completely but forgot I was missing how I would be able to filter out a property that has been inherited.

    Let's say I have 2 tables I created in MVC.

    Table 1
    Cars

    //Here we also have two tables "Sedan" and "SUV". Because they inherit Cars MVC mashed them up into one table "Cars".

    Table 2
    Salesman

    My Query is as follows:

               var statsModel =  (
                    from c in db.Cars
                        join s in db.Salesman on c.SalesmanId equals c.ID into sales
                    select new { s, sales }
                    into data
                    group data by new { data.c.Color, data.c.Year } into g
                    select new MyData
                    {
                        CarYear = g.Key.Year,
                        SomeCount = g.Count(d => (d.c.Status == "Sold"),
                        MySum = g.Where(d => d.t.HasPremium == true && (d.t.Status == "Sold").Sum(d => d.t.Value)
                    }
                ).ToList();

    The above does not work since HasPremium is a "SUV" property (not in Cars) so I am wondering how I would be able to add in this type into this one query. I hope this makes sense!

    I tried doing this but got an error "DbOfTypeExpression requires an expression argument with a polymorphic result type that is compatible with the type argument."

    MySum = g.OfType<CarsSUV>().Where(d => d.HasPremium == true && (d.Status == "Sold").Sum(d => d.CarValue)
    



    Thanks for your help.

    Monday, July 8, 2019 6:05 PM

Answers

  • User475983607 posted

    MVCNewbi3v

    Why is there no way as i said above to create one linq query that groups all the data together by let's say "Name", gets get a count of all Courses, Count of all Courses that are of type OfflineCourse with Address == "Test", All courses who the Trainer == "Trainer Name", etc... 

    If that makes sense, i just want to know if it is possible or not. If it's not possible, i'll just try to work around the problem and split the data into multiple parts.

    Inheritance works in one direction.  A Student is a type of Person.  So you can do this...

    Person p = new Student();

    ...which can be is polymorphic.  If Person has a virtual Find() method and Student overrides Find(), p will invoke the Student's Find method because it is really a Student type not a Person.  

    However, you can not do this...

    Person p = new Person();

    ... and invoke the Student Find() method.  This is basically what you are trying to do except with properties and why you are getting the polymorphic error message.

        public class Person
        {
            public string Name { get; set; }
            public virtual string Find()
            {
                return "Find from Person";
            }
        }
    
        public class Student : Person
        {
            public string StudentId { get; set; }
            public override string Find()
            {
                return "Find from Student";
            }
        }

    This means you can can create a collection of the base types and have a mixture of base types and derived types.

    In order to achieve what you are trying to achieve, you can create two projection queries and fill an array of base types.  But, you'll end up with essentially Apples and Oranges and need to test the type before accessing a property that may or may be part of the type.

    IMHO, this is a object model design issue.  I think moving away from inheritance will help.

    SO Reference which might be of help.

    https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 8:16 PM
  • User1120430333 posted

    I read that the inheritance you are trying to do does not work with Linq-2-Entities,  becuase T-SQL must be generated by the EF engine,  and it can't be done. Therefore, you need to take a another approach, like getting the objects into a collection and using Linq-2-Objects that does not involve EF, possibly.

    Keep in mind that you are working with a collection of objects in a List<T> that EF uses that are retuned by EF as a result of a Linq query.

    If it's not viable, then find another way of doing it or don't do it.

    https://php.wekeepcoding.com/article/21087931/Entity+Framework+doesn%27t+query+derived+classes+-+Error+in+DbOfTypeExpression

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 9:29 PM

All replies

  • User1120430333 posted

    MVC a UI design pattern has nothing to do with inheritance. So lets get that straight. :)

    https://www.codeproject.com/Articles/228214/Understanding-Basics-of-UI-Design-Pattern-MVC-MVP

    Inheritance is a pillar of OO.

    https://www.c-sharpcorner.com/UploadFile/e6a07d/pillars-of-oop/

    Your issues are with OOP and  Linq-2-Entities and not with MVC.

    Monday, July 8, 2019 8:14 PM
  • User1901201124 posted

    Right, its a Linq 2 Entities issue, so what is the best way to resolve this? It is not possible to do it in one query like i have it?

    Tuesday, July 9, 2019 12:18 PM
  • User1120430333 posted

    I would say make separate linq-2-entities queries, disconnect from the DB, merge the results of the queries into one collection and use Linq-2-Object

    Tuesday, July 9, 2019 3:28 PM
  • User1901201124 posted

    Wow really!

    The data belong together as I am doing multiple counts using the group by. I am not sure how I would have 1 query for:
    Year
    SomeCount

    Then another query that is:
    HelpWithCount

    And then merge them as one record. It really odd linq to entities doesn't handle inheritance better.

    Tuesday, July 9, 2019 4:23 PM
  • User475983607 posted

    Entity Framework Inheritance is covered here...

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application

    It is not real clear what "HasPremium" means but it feels like the property should be a base class property since you want the property in the Cars type.  Query the SUV type to get to the HasPremium as it is only available in the SUV type not the Cars type.

    An SUV is a type of Car and has all the Car properties but a Car is not an SUV and does not have SUV properties.  This points to a design issue.

    Tuesday, July 9, 2019 5:53 PM
  • User1901201124 posted

    I just made it up as an example. It's not my model. But thought someone would be able to help, over looking the model does not really "make sense". I just need to be able to query those property in the sub class.

    Like why is it not possible to create one linq query that gets all Cars, Count cars that have status sold, count cars that have status sold and of SUV, Get all Cars that are SUV and Sum up the SUV's value, etc.. 

    I am 100% my model is correct. Was told "g is of type IGrouping, which is not in your inheritance hierarchy for CarsSUV. I am just not sure how to set up the linq query to be able to handle querying Cars, the types of cars and it's properties and do counts etc... 

    Tuesday, July 9, 2019 5:59 PM
  • User475983607 posted

    MVCNewbi3v

    I just made it up as an example. 

    The example has a design bug.  But, the example does not reflect what you are trying to do?  

    Tuesday, July 9, 2019 6:07 PM
  • User1901201124 posted

    If you want a model that makes more sense than please let's refer to the "Table-per-Hierarchy (TPH)" example at

    https://www.dotnettricks.com/learn/entityframework/understanding-inheritance-in-entity-framework

    Now it goes back to the same problem of creating one linq query that is able to group all Courses by Name, count All Courses, count All Courses of Type OfflineCourse and have address == "test", etc...

    Tuesday, July 9, 2019 6:09 PM
  • User1120430333 posted

    You can merge two collections that have the same object types into a single  collection of objects.

    ttps://www.dotnetperls.com/concat

    Tuesday, July 9, 2019 7:09 PM
  • User1901201124 posted

    Not understanding how this will help, maybe i'am confusing everyone. But i find it messed up that no one has done this before.

    If you look at this model https://www.dotnettricks.com/learn/entityframework/understanding-inheritance-in-entity-framework

    Why is there no way as i said above to create one linq query that groups all the data together by let's say "Name", gets get a count of all Courses, Count of all Courses that are of type OfflineCourse with Address == "Test", All courses who the Trainer == "Trainer Name", etc... 

    If that makes sense, i just want to know if it is possible or not. If it's not possible, i'll just try to work around the problem and split the data into multiple parts.

    Tuesday, July 9, 2019 7:22 PM
  • User475983607 posted

    MVCNewbi3v

    Why is there no way as i said above to create one linq query that groups all the data together by let's say "Name", gets get a count of all Courses, Count of all Courses that are of type OfflineCourse with Address == "Test", All courses who the Trainer == "Trainer Name", etc... 

    If that makes sense, i just want to know if it is possible or not. If it's not possible, i'll just try to work around the problem and split the data into multiple parts.

    Inheritance works in one direction.  A Student is a type of Person.  So you can do this...

    Person p = new Student();

    ...which can be is polymorphic.  If Person has a virtual Find() method and Student overrides Find(), p will invoke the Student's Find method because it is really a Student type not a Person.  

    However, you can not do this...

    Person p = new Person();

    ... and invoke the Student Find() method.  This is basically what you are trying to do except with properties and why you are getting the polymorphic error message.

        public class Person
        {
            public string Name { get; set; }
            public virtual string Find()
            {
                return "Find from Person";
            }
        }
    
        public class Student : Person
        {
            public string StudentId { get; set; }
            public override string Find()
            {
                return "Find from Student";
            }
        }

    This means you can can create a collection of the base types and have a mixture of base types and derived types.

    In order to achieve what you are trying to achieve, you can create two projection queries and fill an array of base types.  But, you'll end up with essentially Apples and Oranges and need to test the type before accessing a property that may or may be part of the type.

    IMHO, this is a object model design issue.  I think moving away from inheritance will help.

    SO Reference which might be of help.

    https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 8:16 PM
  • User1901201124 posted

    OK I get it and I totally agree! Looks like creating the inheritance just complicated retrieving the data more than worth.

    Breaking the inheritance would mean fixing all this in the web app as well. *SIGH* 

    OR

    Maybe using "Table-per-Type (TPT)" Design I can just use some joins to figure out the properties.


    Arggg frustrating lol Need to call it a day and re-think about this!

    Tuesday, July 9, 2019 8:27 PM
  • User1120430333 posted

    I read that the inheritance you are trying to do does not work with Linq-2-Entities,  becuase T-SQL must be generated by the EF engine,  and it can't be done. Therefore, you need to take a another approach, like getting the objects into a collection and using Linq-2-Objects that does not involve EF, possibly.

    Keep in mind that you are working with a collection of objects in a List<T> that EF uses that are retuned by EF as a result of a Linq query.

    If it's not viable, then find another way of doing it or don't do it.

    https://php.wekeepcoding.com/article/21087931/Entity+Framework+doesn%27t+query+derived+classes+-+Error+in+DbOfTypeExpression

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 9, 2019 9:29 PM
  • User1901201124 posted

    I switch the implementation to a Table-per-Type (TPT) and it created a "Course", "OfflineCourse" and "OnlineCourse" instead of mashing everything into one table.
    This allowed me to add the joins I needed to filter out properties of the "OfflineCourse" 

    MyOfflineCourseSum = g.Select(d => d.offlinecourse.Where(o => o.Address == "test" && (d.c.Trainer == "Jim"))),

    This is great , it grabbed all the courses that are offline and had address == "test". But one problem is because in my project "OfflineCOursse" has more than 1 field so it selects all the fields. How do I just select the field "Price" and then do a Sum of the Price?

    If this is possible then this may be a suitable answer for me instead of designing the project.

    Wednesday, July 10, 2019 1:10 PM
  • User475983607 posted

    This is great , it grabbed all the courses that are offline and had address == "test". But one problem is because in my project "OfflineCOursse" has more than 1 field so it selects all the fields. How do I just select the field "Price" and then do a Sum of the Price?

    Use Select() to select the fields in the result.

    Wednesday, July 10, 2019 1:36 PM
  • User1901201124 posted

    OK got the select. Getting closer.

    MyOfflineCourseSum = g.Select(d => d.offlinecourse.Where(o => o.Address == "test" && (d.c.Trainer == "Jim")).Select(o => o.Price)),

    Which getting only the Price field. Now is it possible to use the Sum on that? if you say use Sum to sum i'm going to have a heart attack! I tried .Sum and it didn't really do a sum but just list all the prices.

    OK Weird I got it to work. I had to use .Sum 2 times and it actually did the sum. wtf.. 

    Wednesday, July 10, 2019 1:49 PM
  • User475983607 posted

    I assume g is a group?

    MyOfflineCourseSum = g.Sum(d => d.offlinecourse.Where(o => o.Address == "test" && (d.c.Trainer == "Jim")).Select(o => o.Price)),

    Wednesday, July 10, 2019 2:14 PM
  • User1901201124 posted

    Yes g is the grouped data

    This is what I did and it worked.

    MyOfflineCourseSum = g.Select(d => d.offlinecourse.Where(o => o.Address == "test" && (d.c.Trainer == "Jim")).Select(o => new { thePrice = o.Price }).Sum(o => o.thePrice)).Sum(),

    Wednesday, July 10, 2019 2:25 PM