1

I have some code:

(from AspNetUsers in db.AspNetUsers
join UserDetails in db.UserDetails on new { Id = Convert.ToInt32(AspNetUsers.UserDetailId) } equals new { Id = UserDetails.Id } into UserDetails_join
where
AspNetUsers.Email == "mickeymouse@blueyonder.co.uk"
select new
{
   AspNetUsersId = AspNetUsers.AspNetUsersId,
   UsertId = (int?)UserDetails.UserID
}).ToList();

(This is a snippet from a much larger query that contains many joins)

Anyway, the problem I have is that the query runs perfectly within the Linger program, but when I copy the query over to VS 2013, It fails because of the Convert.ToInt32.

Cannot figure out why?

gilesrpa
  • 969
  • 1
  • 12
  • 35
  • What does the exception say? – leppie Jun 22 '15 at 12:02
  • 3
    You should clarify exactly what the problem is "It fails because of the Convert.ToInt32" doesn't tell us much. Is there a compiler problem? A runtime exception (if so what)? etc. – Chris Jun 22 '15 at 12:03

2 Answers2

3

You can't use a Convert.ToInt32 in linq to entities.

One way would be to do all this "in memory" (enumerate all).

But of course that's really bad for performance.

One other way, in your case, would be

First : do it the other way in the join (convert the int in a string : cause this way is doable in linq to entities).

=> new { Id = AspNetUsers.UserDetailId } equals new { Id = SqlFunctions.StringConvert((double)UserDetails.Id) }

Then : get only the data you need, enumerate and cast in linq to objects.

select new
{
   AspNetUsersId = AspNetUsers.AspNetUsersId,
   UsertId = UserDetails.UserID
}).ToList()
.Select (m => new {
   m => m.AspNetUsersId,
   UsertId = (int?)m.UsertId
});

Finally : the best way would be not to store int as varchar...

Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
0

You can use cast (i.e (int?)AspNetUsers.UserDetailId) instead of Convert and EF providers should implement it (is related to the provider). SQL Server EF provider probably converts it to CAST AS

bubi
  • 6,414
  • 3
  • 28
  • 45