locked
How to express this SQL query in EF6? RRS feed

  • Question

  • User-173651909 posted

    How would you express the following query in EF6?

    select count(componentid), componentid from mytable
    where computerid = 83
    group by componentid

    I have a table with the recordid, computerid then componentid. If there are four distinct records with the same componentid then i need to display it as quantity.

    computerid     componentid
    ----------     -----------
    23                13
    23                15
    23                7
    23                3
    23                3
    23                15
    24                2
    24                1
    

    So if searched for computerid 23's components (which i pass to the controller as id) I need to return:

    id       count

    3         2
    13       1
    7         1
    15       2

    Then I can pass it to the view with component id and quantity.

    Thanks in advance
    Adam

    Tuesday, December 8, 2015 9:58 AM

Answers

  • User-271186128 posted

    Hi Adam,

    You could try to use the following code:

    using (MyTestDBEntities context = new MyTestDBEntities())
    {
        var query = from tt in context.mytable
                    where tt.computerid ==83
                    group tt by tt.componentid into group
                    select new 
                    {
                        id = group.Key,
                        count = group.Count()
                    };
    }

    For more details LINQ query statement, please refer to the following article:

    https://msdn.microsoft.com/en-us/library/gg509017.aspx

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2015 5:32 AM

All replies

  • User-330204900 posted

    I think this is sort of it

    var test = from c in mytable
               group c by componentid
               where computerid == 83
               select new { count = count(componentid), ComponetId = componentid };
    

    have a look at this LINQ Query Expressions (C# Programming Guide) that should help

    Tuesday, December 8, 2015 11:44 AM
  • User-173651909 posted

    I get:

    "The name count does not exist in the current context"

     count = count(componentid)
    Tuesday, December 8, 2015 12:12 PM
  • User-271186128 posted

    Hi Adam,

    You could try to use the following code:

    using (MyTestDBEntities context = new MyTestDBEntities())
    {
        var query = from tt in context.mytable
                    where tt.computerid ==83
                    group tt by tt.componentid into group
                    select new 
                    {
                        id = group.Key,
                        count = group.Count()
                    };
    }

    For more details LINQ query statement, please refer to the following article:

    https://msdn.microsoft.com/en-us/library/gg509017.aspx

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 9, 2015 5:32 AM