Compilation error while trying to join two tables in Entity Framework LINQ query

Question

          List<VideoInfo> vobj = new List<VideoInfo>();
         vobj = (from vid in db.VideoInfoes.Where(c => c.IsActive == true)
                orderby vid.VideoId  descending
                select vid
                ).ToList();
        return View(vobj);

This is the orignal Query to bring the list of all the video info. There is another table called profile which has the Profile Picture which I need along with the video Info. So after going through an article on EF I came up with some thing like this..

  vobj = (from vid in db.VideoInfoes.Where(c => c.IsActive == true)
                 select new
                {
                 ProfileId = vid.ProfileId,
                 ProfilePictureUrl = vid.ProfileInfo.ProfilePictureUrl
                }
                orderby vid.VideoId  descending
                 select vid
                ).ToList();
        return View(vobj);

ProfileId is the foreign key. But this is not even compiling..Its showing red syntax error after the closing curly brackets and orderby.

cuongle · Accepted Answer · 2013-05-05 03:52:42Z

2

You should do OrderBy before Select since the property you want to order is not contained in your new select:

var vobj = (from vid in db.VideoInfoes.Where(c => c.IsActive == true)
        orderby vid.VideoId  descending
        select new
            {
             ProfileId = vid.ProfileId,
             ProfilePictureUrl = vid.ProfileInfo.ProfilePictureUrl
            }
        ).ToList();

Aslo I feel more comfortable if using lambda syntax:

var vobj = db.VideoInfoes.Where(c => c.IsActive)
          .OrderByDescending(c => c.VideoId)
          .Select(c => new {
                ProfileId = vid.ProfileId,
                ProfilePictureUrl = vid.ProfileInfo.ProfilePictureUrl
            }).ToList();

edited May 5, 2013 at 3:52

answered May 5, 2013 at 3:42

cuongle

75.5k30 gold badges155 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Scorpio Over a year ago

yes I tried this.. but vobj is of type VideoInfo so its not allowing me to do ToList..It says cannot convert anonymous type to Generic type VideoInfo

cuongle Over a year ago

@Scorpio: edited, you don't need to declare ` List<VideoInfo> vobj = new List<VideoInfo>();` since vobj is now anonymous type, use var instead

Dave Cousineau Over a year ago

@Scorpio select new will project the sequence into an enumerable of anonymous type. If you want an enumerable of VideoInfo you should just select vid as you had at the end. (you don't even need a select clause if you use the method chain syntax in that case).

Scorpio Over a year ago

@CuongLe I dont get this... My view is expecting type @ model IEnumerable<Data.VideoInfo> ...how can I use var ?? its bit confusing at this poin for me

cuongle Over a year ago

@Scorpio: You need to take a look anonymous type in C#

|

Dave Cousineau · Accepted Answer · 2013-05-05 04:03:51Z

1

Your function can be corrected and made much simpler as follows:

return View(
   db
   .VideoInfoes
   .Where(videoInfo => videoInfo.IsActive)
   .OrderByDescending(videoInfo => videoInfo.VideoID)
   .ToList());

You will be able to access the ProfileInfo of each VideoInfo in the list, so long as the db connection is not disposed. Or you can access other ProfileInfo records using the ProfileId property in another query with another context later. It depends what you would like to do with the data; (I'm not sure what the View function is doing, for example.)

For example:

// where 'data' was what was returned from the above query
// and assuming the above 'db' connection is still open
foreach (VideoInfo videoInfo in data) 
{
   ProfileInfo profileInfo = videoInfo.ProfileInfo;

   // do something with profileInfo
}

// or if the connection was disposed:
using (MyEFModel db = new MyEFModel())
   foreach (VideoInfo videoInfo in data) 
   {
      int profileID = videoInfo.ProfileId;
      ProfileInfo profileInfo =
         db
         .ProfileInfoes
         .Single(row => row.ID == profileID);

      // do something with profileInfo
   }

edited May 5, 2013 at 4:03

answered May 5, 2013 at 3:57

Dave Cousineau

13.4k8 gold badges70 silver badges83 bronze badges

1 Comment

Scorpio Over a year ago

thts true the primary - foreign key is already mapped in DB ..I dont know what was I thinking.. The previous query work's correctly ...its just that I have to go one level deep i.e. VideoInfoModel.ProfileInfo.ProfilePictureUrl...anyways thank for all the help..

Collectives™ on Stack Overflow

Compilation error while trying to join two tables in Entity Framework LINQ query

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related