How can this Linq2Sql create an enum in a select clause?
I have the following linq2sql query and I am setting the result to POCO. One of my POCO properties is enum.
public IQueryable<Models.Achievement> GetAchievements()
{
return from a in _sqlDatabase.Achievements
select new Models.Achievement
{
// Note: ToEnum is an extension method that converts an int -> the enum.
AchievementType = a.AchievementTypeId.ToEnum<Models.AchievementType>(),
DateTimeCreated = a.DateTimeCreated,
UserId = a.UserId
};
}
When I run the request, I get the following error.
System.NotSupportedException: Method 'Models.AchievementType
ToEnum[AchievementType](int)' has no supported translation to SQL.
hmm. is there a way I can be tricky enough to make the int result converted to my custom enum?
source to share
In fact, you can use enums directly in LINQ-to-SQL (but not in the Entity Framework); just change the property type (in dbml / designer) to a fully qualified enum type and it will do the throw for you. Of course, you can call it "AchievementType" and this assumes a direct (cast) translation.
If that doesn't fit your scenario, you probably have to select it as an integer and then do the final conversion to LINQ-to-Objects (via AsEnumerable ()). You can also try straightforwardness (if that's enough) instead of your generic extension method:
select new {..., AchievementType = (AchievementType) foo.Bar, ...}
Not that if the type is Models.Achievement
defined in the dbml, you cannot create it that way - you just need to select it. But if it's not part of the db model then that's ok. Here's an example of string mapping for enums in LINQ-to-SQL.
If neither of these suits, another approach is to declare the enum property as a shim in the partial class. I've provided an example of this here (for EF, but it works the same), but note that if you do this, you won't be able to use the enum property in Where
etc., as it doesn't show up in the model.
source to share