Convert SQL query to Entity Framework

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! 

Answer 1

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.

Answer 2

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.