Linq to SQL query SubQuery

Question

I have a follow up question on the question I asked here

here are all of my Linq query

This is query 1:

var RCode = from a in DbEntity.MobileAssetDowntimeReasonCodes 
            where a.MobileAssetCategoryId.Equals(reasonCode) 
            select new 
            { 
                a.JdeReasonCode 
            };

ReasonCode.DataSource = RCode.ToList();
ReasonCode.DataBind();

this brings a resulting codes that are displayed in DataGrid.

here is Query 2 which also works

var RJDEReasonCode = from a in JDETable.F0005
                                 where
                                   a.DRSY.Equals("00") &&
                                   a.DRDL01 != null &&
                                   (a.DRRT.Equals("W4") ||
                                   a.DRRT.Equals("W5")) &&
                                   a.DRKY.Trim() == "801"
                                 select new
                                 {
                                     CATEGORY_CODE = a.DRRT,
                                     REASON_CODE = a.DRKY,
                                     DESCRIPTION = a.DRDL01
                                 };

But instead of hardcoding "801" I want to pass the result of the query1 to query 2 and display query 2 results in my dataGrid. how do I do that ?

Does query #1 return `801`? Try `a.DRKY.Trim() == RCode.FirstorDefault().JdeReasonCode` — Guru Stron, Jul 11 '13 at 19:25
query one returns a list of codes such as 801 , 802,803 and I want to run each code in the second query to get the details of that code and display that — Aaron, Jul 11 '13 at 19:27
You could do that in a loop, and concatenate the results of each iteration to a single collection. Or you could modify your LINQ statement to be something similar to: `RCode.Contains(a.DRKY.Trim())` — Curtis Rutland, Jul 11 '13 at 19:34
Have a read of [this answer](http://stackoverflow.com/a/5456606/1466627). — Bob., Jul 11 '13 at 19:50

Bob. · Accepted Answer · 2013-07-11T20:08:47.097

2

Something like this?

List<string> RCode = DbEntity.MobileAssetDowntimeReasonCodes
    .Where(a=>a.MobileAssetCategoryID.Equals(reasonCode))
    .Select(a=>a.JdeReasonCode).ToList();

var RJDEReasonCode = JDETable.F0005
    .Where(a=>a.DRSY.Equals("00") && a.DRDL01 != null &&
             (a.DRRT.Equals("W4") || a.DRRT.Equals("W5")) &&
              RCode.Any(code => code.Contains(a.DRKY.Trim())))
    .Select(a=>new { CATEGORY_CODE = a.DRRT,
                     REASON_CODE = a.DRKY,
                     DESCRIPTION = a.DRDL01
    });

Uses the Enumerable.Any Method to check if the retrieved RCodes are one of the codes in your F0005 table.

edited Jul 11 '13 at 20:08

answered Jul 11 '13 at 19:45

Bob.

3,894
4
44
76

Syntax is missing something .Select(a=>a.JdeReasonCode).ToList()); and .Select(a=>new { CATEGORY_CODE = a.DRRT, shows error it says 'bool' does not contain a definition for 'select' and no extension method .. – Aaron Jul 11 '13 at 19:52
@user2315840 Sorry, I was missing a bracket on each one. – Bob. Jul 11 '13 at 20:09
this Worked. Thanks So much learning Linq is an wonderful experience. – Aaron Jul 11 '13 at 23:18
@user2315840 Gotta start somewhere! – Bob. Jul 12 '13 at 11:34

Guru Stron · Answer 2 · 2013-07-11T20:10:28.223

0

var RJDEReasonCode = from a in JDETable.F0005
                     where
                         a.DRSY.Equals("00") &&
                         a.DRDL01 != null &&
                         (a.DRRT.Equals("W4") ||
                         a.DRRT.Equals("W5")) &&
                         //and use it checking if it contains a.DRKY.Trim() 
                         RCode.Contains(a.DRKY.Trim())
                      select new
                      {
                                     CATEGORY_CODE = a.DRRT,
                                     REASON_CODE = a.DRKY,
                                     DESCRIPTION = a.DRDL01
                      };

edited Jul 11 '13 at 20:10

answered Jul 11 '13 at 19:39

Guru Stron

102,774
10
95
132

can you elaborate your answer a bit. I am not understanding this as I am new to Linq – Aaron Jul 11 '13 at 19:45
Does not work it says Contains has some invalid argument and second error says Argument1: connot convert from 'string to ' anonymoustype#1' – Aaron Jul 11 '13 at 20:05
what type is `JdeReasonCode` – Guru Stron Jul 11 '13 at 20:06
JdeReasonCode shows linq type lines when i put break point on it – Aaron Jul 11 '13 at 22:27

Linq to SQL query SubQuery

2 Answers2