Writing a subquery using LINQ in C#

Question

I would like to query a DataTable that produces a DataTable that requires a subquery. I am having trouble finding an appropriate example. This is the subquery in SQL that I would like to create:

SELECT *
    FROM SectionDataTable
    WHERE SectionDataTable.CourseID = (SELECT SectionDataTable.CourseID
                                       FROM SectionDataTable
                                       WHERE SectionDataTable.SectionID = iSectionID)

I have the SectionID, iSectionID and I would like to return all of the records in the Section table that has the CourseID of the iSectionID.

I can do this using 2 separate queries as shown below, but I think a subquery would be better.

string tstrFilter = createEqualFilterExpression("SectionID", strCriteria);
tdtFiltered = TableInfo.Select(tstrFilter).CopyToDataTable();
iSelectedCourseID = tdtFiltered.AsEnumerable().Select(id => id.Field<int>("CourseID")).FirstOrDefault();

tdtFiltered.Clear();
tstrFilter = createEqualFilterExpression("CourseID", iSelectedCourseID.ToString());
tdtFiltered = TableInfo.Select(tstrFilter).CopyToDataTable();

This post might help: [SO](http://stackoverflow.com/questions/418609/how-to-do-subquery-in-linq) — David Tansey, Apr 30 '14 at 19:49

score 2 · Accepted Answer · answered Apr 30 '14 at 19:48

2

Although it doesn't answer your question directly, what you are trying to do is much better suited for an inner join:

SELECT *
FROM SectionDataTable S1
   INNER JOIN SectionDataTable S2 ON S1.CourseID = S2.CourseID
WHERE S2.SectionID = iSectionID

This then could be modeled very similarily using linq:

 var query = from s1 in SectionDataTable
     join s2 in SectionDataTable
     on s1.CourseID equals s2.CourseID
     where s2.SectionID == iSectionID
     select s1;

answered Apr 30 '14 at 19:48

Mike Dinescu

54,171
16
118
151

Thanks for the information. I tried your linq code and am getting a compile error that it does not recognize the 'join' keyword. I am using System.Linq. I am currently troubleshooting this error. – Gloria Santin Apr 30 '14 at 20:30
2

I figured it out. I'm sure you were assuming I new more about LINQ. But I didn't. I needed to add the .AsEnumerable() to the DataTables. This is final code: `var query = from sectionTbl1 in TableInfo.AsEnumerable() join sectionTbl2 in TableInfo.AsEnumerable() on sectionTbl1.Field("CourseID") equals sectionTbl2.Field("CourseID") where sectionTbl2.Field("SectionID") == iSectionID select sectionTbl1;` – Gloria Santin May 01 '14 at 12:37
Glad to hear you were able to figure it out Gloria! – Mike Dinescu May 01 '14 at 15:20

score 0 · Answer 2 · answered Apr 30 '14 at 21:01

When working in LINQ you have to think of the things a bit differently. Though you can go as per the Miky's suggestion. But personally I would prefer to use the Navigational properties.

For example in your given example I can understand that you have at-least 2 tables,

Course Master
Section Master

One Section must contain a Course reference

Which means

One Course can be in multiple Sections

Now if I see these tables as entities in my model I would see navigational properties as,

Course.Sections    //<- Sections is actually a collection
Section.Course     //<- Course is an object

So the same query can be written as,

var lstSections = context.Sections.Where(s => s.Course.Sections.Any(c => c.SectionID == iSectionID)).ToList();

I think you main goal is, you are trying extract all the Sections where Courses are same as given Section's Courses.

I would like to query a DataTable. The DataTable binds to a gridview. I don't know about Entity Framework. But what I am read, it would mean I would have to generate entities from the table. I just want a simple query create a filtered DataTable of all of the sections with a CourseID. — Gloria Santin, May 01 '14 at 12:24
I thought you are talking about Entity Framework which your question's Tag reflect. — sallushan, May 01 '14 at 13:39

Writing a subquery using LINQ in C#

2 Answers2