How to do a Group by while joining three tables in LINQ C#

Question

I am trying to do a groupby while joining three tables in LINQ. Below is my code. Any suggestions on how to do this will be a great help.

var result = (from cm in DB.BRAND_NAME_MAP_COMMENTs
          join R in DB.BRAND_NAME_MAP_MASTERs on cm.BRAND_NAME_MAP_ID equals R.BRAND_NAME_MAP_ID
          join K in DB.V_BRANDNAME_INGREDIENT_MAPs on R.BRAND_NAME_MAP_ID equals K.BRAND_NAME_MAP_ID
          orderby cm.UPDATED_TS descending
          group K.PFC_DESC by K.RAND_NAME_MAP_ID into L  //this where the problem is                          
          select new WorkflowComment
          {
              NAMEVersion   = L.PFC_DESC + "-" + K.PFC_VERSION,
              BRAND_NAME    = R.BRAND_NAME,
              HC_BRAND_NAME = R.HC_BRAND_NAME,
              NAME          = K.PFC_DESC,
              //VERSION     = K.PFC_VERSION,
              COMMENTS      = cm.COMMENTS.Replace("\n", "<br />"),
              UPDATED_BY    = cm.UPDATED_BY,
              UPDATED_TS    = cm.UPDATED_TS
          }).ToList();

I still don't understand people's obsession with writing stuff like this instead of just using the database engine to write complex queries...it's like having an axe to cut down a tree, but instead of using it, you use a dull knife. — Ryan Wilson, Jun 05 '18 at 18:47
Well, if they don't have the knowledge, taking the time to understand it, might be to much. If they were taught only a code first approach f.x. They won't have a good time. That being said, there is a level of professionalism to know all aspects of the tool you are using. Thus learning the strength and weaknesses of a such a powerful tool as the Linq query system, is quite a valid practice. I don't actually understand people who question others for their pursuit of knowledge. Unfortunately I don't have the skill to solve this issue for the OP. I wish I did. — Morten Bork, Jun 05 '18 at 18:52
@RyanWilson Well I don't wanna change the entire logic to use an axe. This is just a small code snippet from a huge dependent code that is used in some scenario. — Programmermid, Jun 05 '18 at 18:52
@MortenBork Not questioning the OP's pursuit of knowledge, just the reasoning behind why not to just write it in SQL instead. Which they just answered. — Ryan Wilson, Jun 05 '18 at 18:54
It would help with an expected result set. And what precisely isn't working. Are you getting an exception? is the result different than what you expect? (ie. what do you expect?) — Morten Bork, Jun 05 '18 at 18:55

score 0 · Accepted Answer · answered Jun 05 '18 at 20:12

0

Just checking, if you read the following link: LINQ: combining join and group by

I feel it's the same problem that you are facing.

answered Jun 05 '18 at 20:12

krezaeim

170
1
7

How to do a Group by while joining three tables in LINQ C#

1 Answers1