locked
Convert SQL query to Entity Framework RRS feed

  • Question

  • Hey everyone!

    I'm having a problem to convert existing SQL query to Entity Framework.

    My problem is:

    I have a table of dogs and breeds which every breed name has 3 temperaments that looks like this:

        NAME    |    TEMP1    |    TEMP2    |    TEMP3
    ------------+-------------+-------------+------------
       Check1   |   Angry     |     Shy     |    Ugly
       Check2   |   Rude      |    Cute     |    Shy
       Check3   |    Bla      |    Angry    |    Smelly

    And I want a result that crosses the temperaments columns and grouping them into this:

      Temp   |   COUNT(*)
    ---------+------------
     Shy     |      2
    Angry    |      2
    .
    .
    .

    And if it's possible it would be great if you can answer this post using Entity Framework (if not it's okay to write it in SQL and I will convert it (if possible) to Entity Framework).

    Thanks alot!

    And the query I figured out is:

    select x.tmp, count(*) cnt
    from (
        select temp1 tmp from mytable
        union all select temp2 from mytable
        union all select temp2 from mytable
    ) x
    group by x.tmp

    Is there any way to convert it to Entity Framework syntax.

    Thanks alot for the help! 

    Tuesday, August 18, 2020 7:13 PM

Answers

  • Hi Talshper,
    First, there is a mistake in your query.
    You need to change 

    union all select temp2 from mytable

    to 

    union all select temp3 from mytable

    Then I convert it to Entity Framework  you can refer to.

    var result1 =(from t in ((from t1 in db.mytables select new { tmp = t1.TEMP1 }).Concat(from t2 in db.mytables select new { tmp = t2.TEMP2 }).Concat(from t3 in db.mytables select new { tmp = t3.TEMP3 }))group t.tmp by t.tmp into g select new { tmp = g.Key, cnx=g.Count()}).ToList();

    The result:

     

    Best Regards,
    Daniel Zhang


    "Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
    We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
    For more information, please refer to the sticky post.

    Wednesday, August 19, 2020 9:06 AM

All replies

  • Hi Talshper,
    First, there is a mistake in your query.
    You need to change 

    union all select temp2 from mytable

    to 

    union all select temp3 from mytable

    Then I convert it to Entity Framework  you can refer to.

    var result1 =(from t in ((from t1 in db.mytables select new { tmp = t1.TEMP1 }).Concat(from t2 in db.mytables select new { tmp = t2.TEMP2 }).Concat(from t3 in db.mytables select new { tmp = t3.TEMP3 }))group t.tmp by t.tmp into g select new { tmp = g.Key, cnx=g.Count()}).ToList();

    The result:

     

    Best Regards,
    Daniel Zhang


    "Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
    We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
    For more information, please refer to the sticky post.

    Wednesday, August 19, 2020 9:06 AM
  • Hi Talshper,
    Has your problem been solved? If it is resolved, we suggest that you mark it as the answer. So it can help other people who have the same problem find a solution quickly.
    Best Regards,
    Daniel Zhang


    "Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
    We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
    For more information, please refer to the sticky post.

    Friday, August 21, 2020 5:19 AM