none
Please help converting SQL to LINQ RRS feed

  • General discussion

  • Hi,

    I'm trying to convert this SQL statement to LINQ

    SELECT     c.CategoryID, c.CategoryName, COUNT(distinct pc.PublicationID) AS theCount
    FROM         PublicationCategories c INNER JOIN
                          PubCategories pc ON c.CategoryID = pc.CategoryID INNER JOIN
                          Publications p ON pc.PublicationID = p.PublicationID INNER JOIN
                          PubTopics t ON p.PublicationID = t.PublicationID
    GROUP BY c.CategoryID, c.CategoryName, t.TopicID, p.PubStatus
    HAVING      t.TopicID = 100 AND p.PubStatus = 1
    ORDER BY c.CategoryName

    So far this is what I've come up with:

                var queryLodged = from c in dbcon.PublicationCategories
                                  join pc in dbcon.PubCategories on c.CategoryID equals pc.CategoryID
                                  join p in dbcon.Publications on pc.PublicationID equals p.PublicationID
                                  join t in dbcon.PubTopics on p.PublicationID equals t.PublicationID
                                  where t.TopicID == 100 && p.PubStatus == 1
                                  group c by new { c.CategoryID, c.CategoryName, t.TopicID, p.PubStatus } into g
                                  orderby g.Key.CategoryName
                                  select new
                                  {
                                      CategoryID = g.Key.CategoryID,
                                      CategoryName = g.Key.CategoryName,
                                      theCount = pc.PublicationID.Count()
                                  }
                                  ;

    However my problem is at "theCount". I dont know how to convert that bit into LINQ without errors.
    Could someone please help me out? Thank you so much
    Tuesday, August 18, 2009 12:10 AM

All replies

  • Hi macr0hard,

    If I understand your data structure correctly, you can refer to the following LINQ query:

    =================================================
    var query = from r in

                (from c in db.PublicationCategories

                 join pc in db.PubCategories on c.CategoryID equals pc.CategoryID

                 join p in db.Publications on pc.PublicationID equals p.PublicationID

                 join t in db.PubTopics on p.PublicationID equals t.PublicationID

                 where t.TopicID == 100 && p.PubStatus == 1

                 select new { c.CategoryID, c.CategoryName, t.TopicID, p.PubStatus, pc.PublicationID })

                 group r by new { r.CategoryID, r.CategoryName, r.TopicID, r.PubStatus } into g

                 orderby g.Key.CategoryName

                 select new

                 {

                     g.Key.CategoryID,

                     g.Key.CategoryName,

                     theCount = g.Select(a => a.PublicationID).Distinct().Count()

                 };
    =================================================

     

    Please try the query and tell me whether it fit your scenario.   Otherwise, please provide us with more detailed information about the data structure and some testing data samples.

     

    Have a nice day!

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, August 19, 2009 7:52 AM
    Moderator
  • Hi Lingzhi

    thanks for attempting to solve it for me. That was what I tried first but unfortunately it didnt work. If it worked it'd be too easy. The error I got is

    Invalid column name 'CategoryID'. Invalid column name 'CategoryName'. Invalid column name 'TopicID'. Invalid column name 'PubStatus'.

    thanks again
    Friday, August 21, 2009 12:46 AM
  • Hi marc0hard,

    The codes work fine at my side.  Could you please provide detailed data structure including the PK/FK information? 

     

    Best Regards,
    Lingzhi

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 21, 2009 2:39 PM
    Moderator
  • We are changing the issue type to “General Discussion” because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to “Question” by opening the Options list at the top of the post window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

    Thanks
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 25, 2009 8:04 AM
    Moderator