Problems combining a function or extension method

问题:

In a database I take data from several aliases are used for one ID (a catch all ID) and the same happens for the value this ID needs to be compared to. Linq doesn't allow me however to use an extension or function to select records by, or I am doing something wrong here.

Let's say that null,"_","", and "Default" are posible values for the Catch All ID, where as any other value should be considered as a seperate value.

I'm looking for a way to generate a linq select which matches aliasses in a clean and maintainable way.

What seems to works is:

using (DataContext context = new DataContext())
{
   var q = (from c in context.BasketContents
            where
            (c.ID+" ").Replace("Default", "").Replace("_", "") 
             == 
            (Value+" ").Replace("Default", "").Replace("_", "")
            select c);
}

alternatively this also works:

where ((c.ID==null |c.ID=="Default" | c.ID=="_" | c.ID=="") & 
       (Value==null |Value=="Default" | Value=="_" | Value=="")) |
       (c.ID==Value))

which is even worse :)

Since this comparison needs to be made in a rather big number of queries I would like to use an extension, or a function, so I tried combining the following:

    private bool IsSameID(string a1, string a2)
    {
        return ((a1 == null | a1 == "Default" | a1 == "_" | a1 == "")
        && (a2 == null | a2 == "Default" | a2 == "" | a2 == "_"))
        | (a1 == a2) ? true : false;
    }

with the query:

using (DataContext context = new DataContext())
{
   var q = (from c in context.BasketContents
            where
            IsSameID(c.ID,Value)
            select c);
}

this throws the following exception: Boolean 'IsSameID(System.String, System.String)' has no supported translation to SQL

I also wrote an extension method which would take care of the replaces:

public static string DeAlias(this string a)
{
   return (a == null | a == "Default" | a == "_" | a == "") ? "" : a;
}

so I could compare ID.DeAlias() == Value.DeAlias(), but this basically has the same problem.

So what am I doing wrong here?

回答1:

LINQ to SQL cannot translate your custom method into SQL. You should either filter your basket contents on client side:

from c in context.BasketContents.AsEnumerable()
where IsSameID(c.ID,Value)
select c

Or do not use custom method

from c in context.BasketContents
where (c.ID == null || c.ID == "Default" || c.ID == "_" || c.ID == "") &&
       c.ID == Value
select c

As I pointed in comments, you also can create method which returns expression of type Expression<Func<BasketContents,bool>> if you want to extract complex filtering from query:

private Expression<Func<BasketContents,bool>> IsSameID(string value)
{
    return (Expression<Func<BasketContents,bool>>)(c => 
         ((c.ID == null || c.ID == "Default" || c.ID == "_" || c.ID == "") &&
          c.ID == value);              
}

In this case query will look like:

context.BasketContents.Where(IsSameID("your value"));