Cannot Cast String to GUID in LINQ to Entities

Question

The following code works fine in LinqPad:

(from u in AspNetUsers
     let userOrders = (from uo in UserOrders where uo.UserId == new Guid(u.Id) select uo)
     select u)

However, when I try to execute the exact same code from my MVC application, it gives me the following error:

var users = (from u in ctx.Users
             let userOrders = (from uo in ctx.UserOrders where uo.UserId == new Guid(u.Id) select uo)
             select u);

Only parameterless constructors and initializers are supported in LINQ to Entities.

I don't recall ever having any issues converting a string to a GUID in Linq to Entities before. Am I missing some reference?

I have already included all the references I could think of:

using System.Linq;
using System.Data.Entity;

Why does it work in LinqPad but not in MVC?

Edit: Oddly enough, this seems to work:

let userOrders = (from uo in ctx.UserOrders where uo.UserId.ToString() == u.Id select uo)

`new Guid(u.Id)` <= you cant do that, (as stated by the Exception message). If you were using Linq to Objects (in memory) you could do it but it cant be translated on the data store. — Igor, Jan 30 '17 at 18:04
Also it seems these are custom tables, if so you probably want a FK of some kind in which case the ids should be of the same type anyways, the best option is to fix the store schema so the types match. — Igor, Jan 30 '17 at 18:08
The schema isn't "custom" per se, it comes from the default AspNetUsers construct from MVC where the user Id, a GUID, is represented as a string. I would prefer not to change it and would like to simply match it against another table. There ought to be some simple function that can convert a string to a `uniqueindentifier`. Any ideas? — user1477388, Jan 30 '17 at 18:12
You could extend the schema and add a computed column on one of those tables where the value is the converted id. So on aspnetusers you could have `UsirIdAsGuid` (or something like that) which is nothing more than a computed column that your EF model also knows about. Then you join on that. — Igor, Jan 30 '17 at 18:16
`Guid.Parse` <= wont work as it cant be translated into a store procedure, ie. the c# code needs to be converted to sql and `Guid.Parse` has no translation. See also [CLR Method to Canonical Function Mapping](https://msdn.microsoft.com/en-us/library/bb738681(v=vs.110).aspx) — Igor, Jan 30 '17 at 18:19
Strange day, up is down and down must be up! I could swear I wouldn't be allowed to call `.ToString()` in Linq to Entities but (as shown above) I can. I think I will just go with that simple solution for now. Thanks, Igor. — user1477388, Jan 30 '17 at 18:27
There's a handful of methods that are translatable to SQL. `ToString` is one, as well as things like `ToUpper`, `ToLower`, `IsNullOrEmpty`, etc. However, the more complicate versions of these are not usually supported, e.g. `ToUpperInvariant` or `IsNullOrWhiteSpace` are *not* supported. Generally, if there's a native SQL function that does the same thing as the C# method, you'll probably be okay, but if not, or it requires multiple SQL functions to achieve the same effect, then you can't use it in your query. — Chris Pratt, Jan 30 '17 at 18:52
If something works in LinqPad ans not in your code (or vice versa), then ensure you are using the same provider, options and types. Adjust provider and references in LinqPad as necessary. That way, it will be much easier to test some queries in LinqPad and it would better reflect your actual code (do the query compiles, what is the generated SQL and estimated performance). — Phil1970, Jan 30 '17 at 21:21
@Phil1970 You are correct. I forgot that LinqPad uses Linq to SQL whereas my MVC app was using Linq to Entities. — user1477388, Jan 31 '17 at 02:52

score 2 · Accepted Answer · edited May 23 '17 at 12:17

Ultimately, I just decided to go with the easier approach of casting my GUID .ToString() like so:

let userOrders = (from uo in ctx.UserOrders where uo.UserId.ToString() == u.Id select uo)

The reason being, the default implementation of AspNetUsers defines the column as being varchar, not uniqueidentifier, so in order to change it I would likely have to do something like this How to make EF-Core use a Guid instead of String for its ID/Primary key which I am not presently interested in doing!

score 1 · Answer 2 · answered Jan 30 '17 at 18:26

When you are inside a context the object is not realized yet. Why are you not just using the auto built in navigation properties anyways?

When you have a foreign key in a database or data structure. That creates a 'navigation' property in Entity Framework. As such you can get to child objects a whole lot more quickly. A lot of times they are automatically given depending on your EF options of lazy versus eager loading. The 'include' forces that navigation to be obtained. So if just wanted the orders (seperate table) from my person table I would interrogate person and then get it's child table. I know also there is the concept of 'realized' with Entity Framework and until you can legitimately put something 'ToList' or 'ToString' where if you try to wire things up too much under the hood before they are realized they will not work.

static void Main(string[] args)
{
      using (var context = new TesterEntities())
      {
        var peopleOrders = context.tePerson.Include("teOrder").First(p => p.PersonId == 1).teOrder.ToList();
        peopleOrders.ForEach(x => Console.WriteLine($"{x.OrderId} {x.Description}"));
      }
}

Cannot Cast String to GUID in LINQ to Entities

2 Answers2