none
SQL to LINQ Conversion RRS feed

  • Question

  • Hi I am trying to convert this SQL query to linq query . Please help

    Select * From
    (SELECT optionsMenu.menuID, optionsMenu.MenuName, optionsMenu.parentMenuID, optionsMenu.MenuKey,
    optionsMenu.Description, optionsMenu.SortOrder, optionsMenu.VideoPath,
    optionsParentMenu.menuName as parentMenuName, optionsLanguages.languageID, optionsLanguages.language
    FROM optionsMenu Inner Join optionsMenu as optionsParentMenu on optionsMenu.ParentMenuID = optionsParentMenu.menuID
    Left Join optionsLanguages On optionsMenu.languageID = optionsLanguages.languageID
    WHERE (optionsMenu.parentMenuID IN
    (SELECT menuID FROM optionsMenu AS optionsMenuTemp WHERE (parentMenuID = 3)))) as optionsMenu

    ctrlnick
    Sunday, March 8, 2009 1:27 AM

All replies

  • I developed the below , But I want it to return a List of Strings

    Please let me know what is possible

    and by the way , The query in the foreach loop returns more than one rows at a time


    var menuIDQuery = from oMenu in db.OptionsMenu
                                 
    where oMenu.ParentMenuID == 3
                                 
    select oMenu;
     
               
    foreach (var m in menuIDQuery)
               
    {
     
                   
    var query = from opMenu in db.OptionsMenu
                                join opParentMenu
    in db.OptionsMenu on opMenu.ParentMenuID equals opParentMenu.MenuID
                               
    where opMenu.ParentMenuID == m.MenuID
                               
    select new
                               
    {
                                   
                                    opMenu
    .MenuName,
                                   
                               
    };
     
     
                   
               
    }


    ctrlnick
    Sunday, March 8, 2009 2:35 AM
  • ctrlctrl said:

    Hi I am trying to convert this SQL query to linq query . Please help

    Select * From
    (SELECT optionsMenu.menuID, optionsMenu.MenuName, optionsMenu.parentMenuID, optionsMenu.MenuKey,
    optionsMenu.Description, optionsMenu.SortOrder, optionsMenu.VideoPath,
    optionsParentMenu.menuName as parentMenuName, optionsLanguages.languageID, optionsLanguages.language
    FROM optionsMenu Inner Join optionsMenu as optionsParentMenu on optionsMenu.ParentMenuID = optionsParentMenu.menuID
    Left Join optionsLanguages On optionsMenu.languageID = optionsLanguages.languageID
    WHERE (optionsMenu.parentMenuID IN
    (SELECT menuID FROM optionsMenu AS optionsMenuTemp WHERE (parentMenuID = 3)))) as optionsMenu


    ctrlnick



     

     from om in dc.optionsMenu  
    join pom in dc.optionsMenu on om.parentMenuID equals pom.menuID  
    join (  
        from ol in dc.optionsLanguages  
        where ol.languageID = om.languageID  
        ).DefaultIfEmpty()  
    where pom.parentMenuID == 3  
    select new 
    {  
      om.menuID,  
      om.menuName,  
      om.parentMenuID,  
      om.MenuKey,  
      om.Description,  
      om.SortOrder,  
      om.VideoPath,  
      parentMenuName = pom.menuName,  
      LanguageID = ((ol != null) ? ol.LanguageID : null),  
      Language = ((ol != null) ? ol.Language : null)  
    }; 

    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Monday, March 9, 2009 1:59 AM
    Answerer
  • Hi Kristofer Thank You for your reply ,

    Infact What I was actually looking for was a C# method and relavant LINQ query for the following , Sorry I didnt want to mislead but I myself was mislead

            public DataTable GetAllVODCategories()
            {
               
                DataTable returnTable = new DataTable();
                DataColumn idColumn = new DataColumn();
                idColumn.DataType = System.Type.GetType("System.Int32");
                idColumn.ColumnName = "MenuID";

                DataColumn parentIDColumn = new DataColumn();
                parentIDColumn.DataType = System.Type.GetType("System.Int32");
                parentIDColumn.ColumnName = "ParentMenuID";

                DataColumn menuNameColumn = new DataColumn();
                menuNameColumn.DataType = System.Type.GetType("System.String");
                menuNameColumn.ColumnName = "MenuName";

               

                SqlConnection objConn = new SqlConnection(@"server=localhost;database=SampleDB;Trusted_Connection=true");

                string queryText = "WITH CTE AS(SELECT MenuID, ParentMenuID,MenuName FROM optionsMenu WHERE ParentMenuID = 3 UNION ALL SELECT c.MenuID, c.ParentMenuID,c.MenuName FROM optionsMenu c JOIN CTE p ON c.ParentMenuID = p.MenuID)SELECT * FROM CTE";
                SqlCommand objCommand = new SqlCommand(queryText, objConn);

                SqlDataAdapter da = new SqlDataAdapter(objCommand);

               

                da.Fill(returnTable);

                return returnTable;
            }


    ctrlnick
    Sunday, March 29, 2009 6:46 AM