Linq To SQL OrderBy, issue when using enums

Question

Hey!

I am having some issues with using the OrderBy extension method on a LINQ query when it is operating on an enum type. I have created a regular DataContext using visual studio by simply dragging and dropping everything onto the designer. I have then created seperate entity models, which are simply POCO's, and I have used a repository pattern to fetch the data from my database and map them into my own entity models (or rather, I have a repository pattern, that builds up and IQueryable that'll do all this).

Everything works just fine, except when I try to apply an OrderBy (outside of the repository) on a property that I have mapped from short/smallint to an enum.

Here are the relevant code bits:

public class Campaign
{
    public long Id { get; set; }
    public string Name { get; set; }
    ....
    public CampaignStatus Status { get; set; }
    ...
}
public enum CampaignStatus : short {
    Active,
    Inactive,
    Todo,
    Hidden
}
public class SqlCampaignRepository : ICampaignRepository
{
...
    public IQueryable<Campaign> Campaigns()
    {
        DataContext db = new DataContext();
        return from c in db.Campaigns
                select new Campaign
                   {
                       Id = c.Id,
                       Name = c.Name,
                       ...
                       Status = (CampaignStatus)c.Status,
                       ...
                   };
     }
}

And then elsewhere

SqlCampaignRepository rep = new SqlCampaignRepository();
var query = rep.Campaigns().OrderBy(c => c.Status);

This triggers the following exception: System.ArgumentException was unhandled by user code Message="The argument 'value' was the wrong type. Expected 'IQMedia.Models.CampaignType'. Actual 'System.Int16'." Source="System.Data.Linq" StackTrace: ved System.Data.Linq.SqlClient.SqlOrderExpression.set_Expression(SqlExpression value) ved System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select) ved System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) ved System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitIncludeScope(SqlIncludeScope scope) ...

(sorry about the danish in there, ved = by/at).

I have tried typecasting the Status to short in the orderBy expression, but that doesn't help it, same if i cast it to the actual enum type as well.

Any help fixing this is greatly appreciated!

Answer 1

What is the relationship between the Campaign class and Campaigns? If Campaigns returns the set of Campaign object, note you can't normally select new a mapped entity.

I wonder if it would work any better if you did the OrderBy before the Select?

One final trick might be to create a fake composable [Function], using trivial TSQL. For example, ABS might be enough. i.e. something like (on the context):

    [Function(Name="ABS", IsComposable=true)] 
    public int Abs(int value) 
    { // to prove not used by our C# code... 
        throw new NotImplementedException(); 
    }

Then try:

  .OrderBy(x => ctx.Abs(x.Status))

I haven't tested the above, but can give it a go later... it works for some other similar cases, though.

Worth a shot...

Answer 2

My DataContext has it's own entity class named Campaign, (living in a different namespace, of course). Also the status column is saved as a smallint in the database, and the LINQ Entity namespace has it's type listed as a short (System.Int16).

The orderby DOES work if I apply it in the query in my repository - this is all a part of a bigger thing though, and the whole idea is to NOT have the repository applying any sort, filtering or anything like that, but merely map the database entity classes to my own ones. This example right there is obviously a bit pointless in which it's pretty much a straight mapping, but in some cases I have localization added into it as well.

Also I forgot to add - the exception obviously doesn't occour till i try to execute the query (ie - calling ToList, or enumerating over the collection).

In the bigger picture this method is being used by a service class which is then supposed to add filtering, sorting and all that - and the point of all this is of course to separate things out a bit, but also to allow easy transition to a different database, or a different OR/M, later on, if that would be the desire.

Ah didn't see that last bit till after I replied - I have not had any experience using the Function attribute yet, but I will not have access to the datacontext in the class where I am supposed to apply the sorting.

Answer 3

Can you specify the type CampaignStatus directly in your DataContext trough the designer? This way the value is automatically mapped to the enum.

Answer 4

That did the trick - awesome, thanks Bruno! :)