none
how i get videos accroding to the category selected in linq RRS feed

  • Question

  • i have two tables videocategories and video and i want to show three columns from video table such as title, description author according to the category selected from the videocategory table

    what will be the linq to sql query for this operation

    Thursday, June 24, 2010 5:31 PM

Answers

  • let us said that you choose "Mystery" and that you have an ID called category that is linked to videocategories

    Obtain the relative Id and then query Video

    var query =from myquery in Video where myquery.ID==myID select new {string Title=myquery.myquery, string Author=myquery.Author, string descrip=myquery.descrip};

    Something like that

    Regards

    • Marked as answer by samcreator Friday, June 25, 2010 8:38 AM
    Thursday, June 24, 2010 5:58 PM
  • Hi Sam,

    If you are wanting to return a subset of the columns from the Videos table, then the way to do it is as Serguey described with "select new { ... }". That creates a new anonymous type. What is returned by "query" will be a set containing zero or more matching records so if you want to access the individual record you have to do something like this:

    foreach (var vid in query)
    {
      Console.WriteLine ("{0} - {1} - {2}", vid.Title, vid.Author, vid.descrip);
    }

    You don't have to use the anonymous type, you can select into a class/struct of your own by using "select new VidInfo( myQuery.Title, myQuery.Author, etc.)".

    I don't think there is much of a performance benefit in selecting a subset of columns unless you have a very lot of columns or you are excluding sizeable data when excluding a column.

    John.

    • Marked as answer by samcreator Friday, June 25, 2010 8:39 AM
    Friday, June 25, 2010 7:53 AM

All replies

  • let us said that you choose "Mystery" and that you have an ID called category that is linked to videocategories

    Obtain the relative Id and then query Video

    var query =from myquery in Video where myquery.ID==myID select new {string Title=myquery.myquery, string Author=myquery.Author, string descrip=myquery.descrip};

    Something like that

    Regards

    • Marked as answer by samcreator Friday, June 25, 2010 8:38 AM
    Thursday, June 24, 2010 5:58 PM
  • Hi,

    I hope there's a 1-M relationship between video and video_categories. In this case, you may want to use the Any method:

    var videos = dc.Videos.Any( v=.v.VideoCategories.CategoryID == categoryID );

    If VideoCategories is the junction table between Video and Categories, then

    var videos = dc.Videos.Any( v=.v.VideoCategories.Category.CategoryID == categoryID );

    Regards,

     


    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, June 24, 2010 7:33 PM
  • Hi Syed,

    What's the difference between using the Any method as you've described and using the associative EntitySet property which would be in the VideoCategories object? Such as:

    VideoCategory categ = (from vc in dc.VideoCategories
      where vc.CategoryId == categoryId
      select vc).Single();
    
    var videos = categ.Videos;
    

    Is it simply that you don't have to fetch the VideoCategory record first?

     

    And then, what's the difference between Any and Where?

    p.s. presumably in your syntax it should be "v=>" rather than "v=."?

    Thanks,

    John.

    Friday, June 25, 2010 7:12 AM
  • sorry for replying late

    But seems like nothing works for me. i am new to linq. please give me a complete quer. To make it more clear i am posting the method i am using


        public  IQueryable    GetVideosByCat(int CategoryID)
           
        {
          
            eld= new ELearnDataContext();
            Videocategory categ = (from vc in eld.Videocategories
                                   where vc.CategoryID == CategoryID
                                   select vc).Single();

            var videos = categ.Videos;

    }

    but it will return all videos column and that is not desired i want some specific columns from videos table according to the categoryID from Categories Table.

     

     

    Friday, June 25, 2010 7:34 AM
  • Hi Sam,

    If you are wanting to return a subset of the columns from the Videos table, then the way to do it is as Serguey described with "select new { ... }". That creates a new anonymous type. What is returned by "query" will be a set containing zero or more matching records so if you want to access the individual record you have to do something like this:

    foreach (var vid in query)
    {
      Console.WriteLine ("{0} - {1} - {2}", vid.Title, vid.Author, vid.descrip);
    }

    You don't have to use the anonymous type, you can select into a class/struct of your own by using "select new VidInfo( myQuery.Title, myQuery.Author, etc.)".

    I don't think there is much of a performance benefit in selecting a subset of columns unless you have a very lot of columns or you are excluding sizeable data when excluding a column.

    John.

    • Marked as answer by samcreator Friday, June 25, 2010 8:39 AM
    Friday, June 25, 2010 7:53 AM
  • This works for me

    public  IQueryable  GetVideosByCat(int CategoryID)
            
        {
           
            eld= new ELearnDataContext();
             var query= from catid in eld.Videos
                       where catid.CategoryID==CategoryID
                       select new
                       {


                           catid.Title,
                           catid.VideoID,
                           catid.Description,
                           catid.Author,
                           catid.DateUploaded,
                           catid.MimeType


                       };


                     
          
                            
            return query;
                        


           
        }

    Thank you all for your valuable support.

    but i think it is returning anonymous type what if i want to return videos type. any suggestion

    Friday, June 25, 2010 8:37 AM
  • but i think it is returning anonymous type what if i want to return videos type. any suggestion


    Nothing straighforward! There are four options that I can think of but none particularly nice

    1. Cut down the definition of the Video class in the O/R designer (you can right-click on the column property that you want to exclude and delete it). However that means that you could never access that column.
    2. Use the O/R designer to copy the Video class to a new class - then delete the unwanted columns from the copy. You have to make sure to add the required associations and such like. Again the
    3. Create a view on Videos in the database and then add that view to the data context.
    4. Instead of creating a new anonymous type, you could create a new video record "select new video() { ... }" but that is fraught with dodgyness because it isn't part of the data context. It would be fine if you only wanted the information read-only, but if you wanted to update the record in any way then you would be causing yourself big headaches.

     

    Friday, June 25, 2010 10:08 AM
  • If the problem is because of the limitation of anonymous types you can return a tuple as well

    Regards

    Friday, June 25, 2010 12:11 PM