locked
Linq with Count and Group By RRS feed

  • Question

  • User1122355199 posted

    I'm porting legacy code that utilized stored procedures to Entity Framework Core.  The stored procedure looks like:

    SELECT  Distinct DiagnosisCode, DiagnosisCodeDescription, Count(*) as Count from tbl_Log_PatientDiagnosisCodes Where DiagnosisCode <> '' And (DateofService > '01/01/2018' And DateofService < '06/30/2018') 
    Group By DiagnosisCode, DiagnosisCodeDescription Order By Count Desc

    I have two questions.  First, how do I convert this to a Linq query?  Second, what considerations should I look at as to whether to keep using stored procedures?  Any help would be appreciated.

    Tuesday, September 29, 2020 2:52 AM

Answers

  • User1120430333 posted

    Thanks for the response.  You cleared up the "Use Lina for Everything" debate for me.  I have many perfectly functional sprocs that I would be happy reusing.  I'm running into problems with how to get the models set up correctly, especially when it involves derived columns such as count.  The query executes against only one table, so I set up a model for that table, tbl_Log_PatientDiagnosisCodes, along with a model, called DiagnosisCodeCountList, for the columns returned which looks like this:

        public class DiagnosisCodeCountList
        {
            [Key]
            public string DiagnosisCode { get; set; }
            public string DiagnosisCodeDescription { get; set; }
            public int Count { get; set; }
        }

    When I execute the code:

                    List<DiagnosisCodeCountList> diagnosisCodes = new List<DiagnosisCodeCountList>();
    
                    string rawSql = "SELECT  Distinct DiagnosisCode, DiagnosisCodeDescription, Count(*) as Count from tbl_Log_PatientDiagnosisCodes Where DiagnosisCode <> '' And (DateofService > '01/01/2018' And DateofService < '06/30/2018') Group By DiagnosisCode, DiagnosisCodeDescription Order By Count Desc";
    
                    diagnosisCodes = ctx.DiagnosisCodeCountList.FromSqlRaw(rawSql).ToList();

    I receive the error message:

    InvalidOperationException: The model item passed into the ViewDataDictionary is of type 'System.Collections.Generic.List`1[MyProject.Models.DiagnosisCodeCountList]', but this ViewDataDictionary instance requires a model item of type 'System.Collections.Generic.List`1[MyProject.Models.tbl_Log_PatientDiagnosisCodes]'.

    I'm not sure how to fix this.

    Your naming convention is wrong for DiagnosisCodeCountList the class is a single class it should be named DiagnosisCodeCount.

    What is the [Key] for that serves no purpose, since the class has nothing to do with EF.

     var results = ctx.DiagnosisCodeCountList.FromSqlRaw(rawSql).ToList();

    diagnosisCodes.AddRange(results.Select(result => new DiagnosisCodeCount()
                {
                    DiagnosisCode = result.DiagnosisCode,
                    DiagnosisCodeDescription = result.DiagnosisCodeDescription,
                    Count = result.Count
                }).ToList());

    Or 

    You can make a List<MyProject.Models.tbl_Log_PatientDiagnosisCodes> and point the result of the T-SQL query back to it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 29, 2020 6:51 PM
  • User475983607 posted

    Come on now.  You've ben on these forums for a long time and the error very clear.  It telling you that you've defined the View's model as...

    List<MyProject.Models.tbl_Log_PatientDiagnosisCodes>

    But passed the type

    List<MyProject.Models.DiagnosisCodeCountList>

    The types must match.  I assume you need to change the View to use List<MyProject.Models.DiagnosisCodeCountList>.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 29, 2020 6:56 PM

All replies

  • User475983607 posted

    I have two questions.  First, how do I convert this to a Linq query? 

    The LINQ documentation is very good an has examples.  I recommend going through the documentation first and learn group y in LINQ.

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/group-query-results

    Second, what considerations should I look at as to whether to keep using stored procedures? 

    I prefer to use stored procedures for complex T-SQL logic.  For some reason folks on these forum want to convert everything to LINQ which I find an unnecessary extra step since LINQ converts to T-SQL.  Ultimately, it's up to you.  Weigh the options and pick a solution that best fits your design.

    Tuesday, September 29, 2020 10:53 AM
  • User1122355199 posted

    Thanks for the response.  You cleared up the "Use Lina for Everything" debate for me.  I have many perfectly functional sprocs that I would be happy reusing.  I'm running into problems with how to get the models set up correctly, especially when it involves derived columns such as count.  The query executes against only one table, so I set up a model for that table, tbl_Log_PatientDiagnosisCodes, along with a model, called DiagnosisCodeCountList, for the columns returned which looks like this:

        public class DiagnosisCodeCountList
        {
            [Key]
            public string DiagnosisCode { get; set; }
            public string DiagnosisCodeDescription { get; set; }
            public int Count { get; set; }
        }

    When I execute the code:

                    List<DiagnosisCodeCountList> diagnosisCodes = new List<DiagnosisCodeCountList>();
    
                    string rawSql = "SELECT  Distinct DiagnosisCode, DiagnosisCodeDescription, Count(*) as Count from tbl_Log_PatientDiagnosisCodes Where DiagnosisCode <> '' And (DateofService > '01/01/2018' And DateofService < '06/30/2018') Group By DiagnosisCode, DiagnosisCodeDescription Order By Count Desc";
    
                    diagnosisCodes = ctx.DiagnosisCodeCountList.FromSqlRaw(rawSql).ToList();

    I receive the error message:

    InvalidOperationException: The model item passed into the ViewDataDictionary is of type 'System.Collections.Generic.List`1[MyProject.Models.DiagnosisCodeCountList]', but this ViewDataDictionary instance requires a model item of type 'System.Collections.Generic.List`1[MyProject.Models.tbl_Log_PatientDiagnosisCodes]'.

    I'm not sure how to fix this.

    Tuesday, September 29, 2020 6:07 PM
  • User1120430333 posted

    Thanks for the response.  You cleared up the "Use Lina for Everything" debate for me.  I have many perfectly functional sprocs that I would be happy reusing.  I'm running into problems with how to get the models set up correctly, especially when it involves derived columns such as count.  The query executes against only one table, so I set up a model for that table, tbl_Log_PatientDiagnosisCodes, along with a model, called DiagnosisCodeCountList, for the columns returned which looks like this:

        public class DiagnosisCodeCountList
        {
            [Key]
            public string DiagnosisCode { get; set; }
            public string DiagnosisCodeDescription { get; set; }
            public int Count { get; set; }
        }

    When I execute the code:

                    List<DiagnosisCodeCountList> diagnosisCodes = new List<DiagnosisCodeCountList>();
    
                    string rawSql = "SELECT  Distinct DiagnosisCode, DiagnosisCodeDescription, Count(*) as Count from tbl_Log_PatientDiagnosisCodes Where DiagnosisCode <> '' And (DateofService > '01/01/2018' And DateofService < '06/30/2018') Group By DiagnosisCode, DiagnosisCodeDescription Order By Count Desc";
    
                    diagnosisCodes = ctx.DiagnosisCodeCountList.FromSqlRaw(rawSql).ToList();

    I receive the error message:

    InvalidOperationException: The model item passed into the ViewDataDictionary is of type 'System.Collections.Generic.List`1[MyProject.Models.DiagnosisCodeCountList]', but this ViewDataDictionary instance requires a model item of type 'System.Collections.Generic.List`1[MyProject.Models.tbl_Log_PatientDiagnosisCodes]'.

    I'm not sure how to fix this.

    Your naming convention is wrong for DiagnosisCodeCountList the class is a single class it should be named DiagnosisCodeCount.

    What is the [Key] for that serves no purpose, since the class has nothing to do with EF.

     var results = ctx.DiagnosisCodeCountList.FromSqlRaw(rawSql).ToList();

    diagnosisCodes.AddRange(results.Select(result => new DiagnosisCodeCount()
                {
                    DiagnosisCode = result.DiagnosisCode,
                    DiagnosisCodeDescription = result.DiagnosisCodeDescription,
                    Count = result.Count
                }).ToList());

    Or 

    You can make a List<MyProject.Models.tbl_Log_PatientDiagnosisCodes> and point the result of the T-SQL query back to it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 29, 2020 6:51 PM
  • User475983607 posted

    Come on now.  You've ben on these forums for a long time and the error very clear.  It telling you that you've defined the View's model as...

    List<MyProject.Models.tbl_Log_PatientDiagnosisCodes>

    But passed the type

    List<MyProject.Models.DiagnosisCodeCountList>

    The types must match.  I assume you need to change the View to use List<MyProject.Models.DiagnosisCodeCountList>.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 29, 2020 6:56 PM
  • User1122355199 posted

    When you're right, you're right.  I got so fixated on the query, I forgot about the view.  My bad.  Thanks for the help.

    Tuesday, September 29, 2020 8:02 PM