How to design nested query for fetching data from tables of MS SQL using Entity Framework?

Question

I have three tables which you can see in following diagram:

My problem is, I want to return all problems, its responses and the count of of seen status i.e. those responses which has not seen yet. I'm using Entity framework. I have used following query:

querySelection = (from problems in db.Problems
              join response in db.Response on problems.Id equals response.QueryId
              join order in db.Msg_Orders on response.Id equals order.Response_Id
              join seen_status in db.Seen_Status on order.Order_Id equals seen_status.OrderId
              select new QuerySelect{
                Problem_State = problems.Problem_State,
                Response = response.Response,
                ResponseCount = /*What code should I write here*/
              }).ToList();

I have 2 problems with my above query:

Expectation: It should return only unique problems and unseen response count

What result getting: As Msg_Orders have multiple problem Ids and above query returning same result multiple times
Not understanding how to add count of unseen status inside above query.

Rohit Sawai · Accepted Answer · 2019-09-29 13:13:18Z

I have used Jeff Mercado's solution and its comment for figuring out what exact I can do and how can I modify query in the form of Entity Framework syntax. For achieving my goal, I needed to use GROUP BY clause with INNER JOIN in the Entity Framework syntax.

I have written following solution which solved above problem:

querySelection = (from problems in db.Problems
              join response in db.Response on problems.id equals response.Query_Id
              join order in db.Msg_Orders on query.id equals order.query_id
              join seen_status in db.Seen_Status on order.Order_id equals seen_status.Order_id
              group new { problems, response, order ,seen_status }
                by new
                {
                    problems.Id,
                    problems.Problem_State,
                    problems.Created_Date,
                    response.Response,
                    seen_status.User_Seen_Status
                } into grp
              orderby grp.Key.Id descending
              select new QuerySelection
              {
                  Id = grp.Key.Id,
                  Problem_State = grp.Key.Problem_State,
                  Created_Date = grp.Key.Created_Date,
                  Response = grp.Key.Response,
                  TotalResp = grp.Count(x => x.seen_status.user_seen == 0) // Counting total number of responses
              }
              ).ToList();

Collectives™ on Stack Overflow

How to design nested query for fetching data from tables of MS SQL using Entity Framework?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related