I'm writing an Entity Framework LINQ query in which I want to parse a string into a UNIQUEIDENTIFIER (aka GUID) as part of the WHERE
clause:
public IEnumerable<User> Find(Guid guid)
{
return dbContext
.Users
.Where(user => Guid.Parse(user.GuidText) == guid);
}
I know this is possible in SQL because I've tested it:
SELECT *
FROM Users
WHERE CAST(GuidText AS UNIQUEIDENTIFIER) = @guid;
However, I haven't found a way to generate the CAST
part. I've tried:
(Guid)user.GuidText
, which generates a compiler error.Convert.ToGuid(user.GuidText)
, but this method doesn't exist.Guid.Parse(user.GuidText)
, but this causes Entity Framework to generate an error when it translates the LINQ query.new Guid(user.GuidText)
, but this causes Entity Framework to generate an error when it translates the LINQ query.(Guid)Convert.ChangeType(user.GuidText, typeof(Guid))
, but this causes Entity Framework to generate an error when it translates the LINQ query.SqlGuid.Parse(user.GuidText)
, but this causes Entity Framework to generate an error when it translates the LINQ query.How can I achieve this? I'm willing to embed SQL in the code as a last resort.
EF runtime actually supports converting from a string to a GUID as a cast operation, but as you find out there is currently no way of expressing this in LINQ that we support.
The best workaround that I can think of is to use Entity SQL:
var objectContext = ((IObjectContextAdapter)db).ObjectContext;
var query = objectContext.CreateQuery<User>(
"SELECT VALUE u FROM Context.Users AS u WHERE CAST(u.GuidText AS System.Guid) = @guid",
new ObjectParameter("guid", guid));