How To Bind Linq Join With ViewModel In Asp.net MVC?

Question

Here I am trying to get the list by joining two table using LINQ and I want ContestantId from ContestantRatings table. But it shows an error in line ContestantId = Convert.ToInt32(CR.ContestantId) which says: 'LINQ to Entities does not recognize the method Int32 ToInt32(System.Object) method, and this method cannot be translated into a store expression.' .
Any help will be highly appreciated.

Below is my code

public List<ContestantRating.Core.ViewModel.ContestantRatingVM> GetContestantRatingList()
{
    var contestantRatingList = (from C in db.Contestants where C.IsActive==true
                                join CR in db.ContestantRatings on C.Id equals CR.ContestantId 
                                select new ContestantRatingVM
                                {
                                    ContestantId  = Convert.ToInt32(CR.ContestantId),
                                    FirstName = C.FirstName,
                                    LastName = C.LastName,
                                    DateOfBirth = C.DateOfBirth,
                                    District = C.District.DistrictName,
                                    Rating = CR.Rating,
                                    RatingId = CR.Id,
                                    RatedDate=CR.RatedDate
                                }).ToList().OrderByDescending(x => x.RatingId);
    return contestantRatingList.ToList();
}

Below is my viewmodel ContestantRatingVM which I used

public class ContestantRatingVM
{
    public int  ContestantId { get; set; }
    public Nullable<int> HomeZoneId { get; set; }
    public string District { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Nullable<System.DateTime> DateOfBirth { get; set; }
    public Nullable<bool> IsActive { get; set; }
    public string Gender { get; set; }
    public string PhotoUrl { get; set; }
    public string Address { get; set; }
    public int RatingId { get; set; }
    public Nullable<decimal> Rating { get; set; }
    public Nullable<System.DateTime> RatedDate { get; set; }
}

What is the typeof `ContestantId` in the `ContestantRatings` table? — , Feb 28 '18 at 08:08
Possible duplicate of [LINQ to Entities does not recognize the method 'Int32 Int32(System.String)' method, and this method cannot be translated into a store expression](https://stackoverflow.com/questions/13887296/linq-to-entities-does-not-recognize-the-method-int32-int32system-string-meth) — Markus, Feb 28 '18 at 08:09
The problem is not related to the join, but to the call to `Convert.ToInt32`. Please see the question above on how to fix this. — Markus, Feb 28 '18 at 08:10
@Markus I tried but it did not worked will you kindly recorrect the my code mistake, that will be a great help — CarrotCrop, Feb 28 '18 at 08:29

score 0 · Answer 1 · answered Feb 28 '18 at 08:43

The problem is the call to Convert.ToInt32. Linq to entities converts the Linq query to a database query and thus has a limited set of operations it supports. In order to solve this, you have several options.

One option is to do the conversion after the database query:

public List<ContestantRating.Core.ViewModel.ContestantRatingVM> GetContestantRatingList()
{
  var tempArray = (from c in db.Contestants where C.IsActive==true
                   join cr in db.ContestantRatings on C.Id equals CR.ContestantId 
                   select new { C = c, CR = cr }).ToArray();
  var contestantRatingList = from x in tempArray select new ContestantRatingVM
                                {
                                    ContestantId  = Convert.ToInt32(x.CR.ContestantId),
                                    FirstName = x.C.FirstName,
                                    // ...
                                }).ToList().OrderByDescending(x => x.RatingId);
  return contestantRatingList.ToList();
}

By calling ToArray, you send the query to the database, so that Linq to Enities does not have to deal with Convert.ToInt32.

Perhaps the real question is why OP would need to use `Convert.ToInt32()` in the first place. The field should be `INT` in the database in the first place, and if its not for some reason, then the typeof `ContestantId` should be changed to match the type in the database. — , Feb 28 '18 at 11:02

How To Bind Linq Join With ViewModel In Asp.net MVC?

1 Answers1