Answered by:
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!
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.- Edited by Daniel_Zhang-MSFTMicrosoft contingent staff Wednesday, August 19, 2020 9:07 AM
- Proposed as answer by KareninstructorMVP Friday, August 21, 2020 12:16 PM
- Marked as answer by Talshper Monday, September 21, 2020 12:34 PM
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.- Edited by Daniel_Zhang-MSFTMicrosoft contingent staff Wednesday, August 19, 2020 9:07 AM
- Proposed as answer by KareninstructorMVP Friday, August 21, 2020 12:16 PM
- Marked as answer by Talshper Monday, September 21, 2020 12:34 PM
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