none
Convert SQL to C# Lambda RRS feed

  • Question

  • Im trying to convert the below SQL syntax to its equivalent C# syntax.

    SELECT     
    Answers.ID, 
    Answers.QuestionID, 
    Answers.AnswerFromQuestion,  
    Questions.ID AS QuID,  
    Questions.QuestionToAsk AS QToAsk

    FROM         
    Answers 

    RIGHT OUTER JOIN Questions ON Answers.QuestionID = Questions.ID

    So far i have

    MyDataContext dc = new MyDataContext();

    var query = dc.Questions.Join(Answers dc.Answers on new {QuestionID = Questions.ID } equals new { QuestionID = Answers.QuestionID } into Answers_join
               from Answers in Answers_join.DefaultIfEmpty()
               select new
               {
                   ID = (int?)Answers.ID,
                   QuestionID = (int?)Answers.QuestionID,
               };

    but the above doesnt compile giving me errors such as when using C#? Using Linquer (external tool) to convert to C# i get 

    from Questions in db.Questions
    join Answers in db.Answers on new { QuestionID = Questions.ID } equals new { QuestionID = Answers.QuestionID } into Answers_join
    from Answers in Answers_join.DefaultIfEmpty()
    select new {
      ID = (int?)Answers.ID,
      QuestionID = (int?)Answers.QuestionID,
    }

    I'm new with Joins etc in C# so dont mind someone giving me a hand on this? Thanks
    Friday, October 3, 2014 12:49 PM

Answers

  • Hello Pure,

    >>but the above doesnt compile giving me errors such as when using C#?

    Could you please share the error message with us? And as far as I know, the DbSet.Join() method does not provide one argument version. Please make sure you call it correctly.

    >>I'm new with Joins etc in C# so dont mind someone giving me a hand on this? Thanks

    Usually, to perform a query with Entity Framework, I would use the LINQ query as below:

    var result = from o in db.Orders
    
                                 join od in db.OrderDetails on o.OrderID equals od.OrderID into oods
    
                                 from ood in oods.DefaultIfEmpty()
    
                                 select new { o, ood };
    

    Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 13, 2014 9:06 AM
    Moderator

All replies

  • Hi,

    Have you tried using tools like LINQPad or Linger?

    Hope this helps

    Friday, October 3, 2014 1:26 PM
  • i feel no needs to use { QuestionID = Questions.ID } .use only Questions.ID equals Answers.QuestionID
    Wednesday, October 8, 2014 12:15 PM
  • Hello Pure,

    >>but the above doesnt compile giving me errors such as when using C#?

    Could you please share the error message with us? And as far as I know, the DbSet.Join() method does not provide one argument version. Please make sure you call it correctly.

    >>I'm new with Joins etc in C# so dont mind someone giving me a hand on this? Thanks

    Usually, to perform a query with Entity Framework, I would use the LINQ query as below:

    var result = from o in db.Orders
    
                                 join od in db.OrderDetails on o.OrderID equals od.OrderID into oods
    
                                 from ood in oods.DefaultIfEmpty()
    
                                 select new { o, ood };
    

    Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 13, 2014 9:06 AM
    Moderator