locked
2 table join with RRS feed

  • Question

  • I'm trying to convert the following query to linqs so I can bind it to an entity datasource for a gridview.  The tables are entities in my EntityDatamodel.  I'm not sure how to write the subquery part.  Please let me know how to convert this to linqs.  Any other tips like using the stored procedure to retrieve the data, would work too.  It didn't work for me using the Sproc because I'm pulling data back from 2 tables and the entity doesn't have properties for both tables.  Hope this makes sense.  Thanks!

    SELECT
        a.CourseID
       ,a.Name
       ,a.Description
       ,a.Schedule
       ,a.Location
       ,a.Schedule
       ,a.StartDate
       ,a.EndDate
       ,a.StartTime
       ,a.EndTime
       ,b.FirstName
       ,b.LastName
       ,a.Size
       ,a.Price
       ,a.DisplayStart
       ,a.DisplayEnd
       ,a.Status
       ,a.InstructorID
       ,a.ModifiedDate
       ,a.CreatedDate
    FROM
        dbo.fcw_course a
       ,dbo.fcw_instructor b
    WHERE
       a.InstructorID = b.InstructorID
       and a.Size > (SELECT COUNT(*) FROM dbo.fcw_registration c WHERE c.CourseID = a.CourseID)
       and a.Status = 'Active'
       and a.DisplayStart <= GETDATE() and a.DisplayEnd >= GETDATE() 

    Tuesday, July 3, 2012 5:09 PM

Answers

  • I would suggest you to use a typed class so that you can manage easily for joined result set.

    e.g.

        public class sample {
            public string a {get; set;}
            public string b {get; set;} 
        }
    
                 using (NorthwindEntities2 ne = new NorthwindEntities2()) {
                 var joinTwo = from c in ne.Categories
                                  join d in ne.Products on c.CategoryID equals d.CategoryID
                                  select new sample{a = c.CategoryName, b= d.ProductName };
    
                }

    for internal sub query you can try this way

    var these = (from c in ne.Categories where 3 > (from d in ne.Products where d.CategoryID == c.CategoryID select d.ProductID).Count() select c);

    /*****If navigation is available*****/

    var those = (from c in ne.Categories where 3 > c.Products.Where(ii=>ii.CategoryID==c.CategoryID).Count() select c);


    hope this helps.



    • Edited by TinMgAye Wednesday, July 4, 2012 4:30 AM
    • Marked as answer by Tony xiao Tuesday, July 17, 2012 11:01 AM
    Wednesday, July 4, 2012 4:29 AM