LINQ to SQL Custom Property query on where clause

Question

I am using LINQ to SQL classes, and have extended one (with a partial class) and added an extra property. I want to query on this property, so:

(from p in db.TableName where p.CustomProperty=="value" select p)

However this doesn't work. I get an error: The member 'TableName.CustomProperty' has no supported translation to SQL. At the moment I have no code in the 'set' section of my Custom Property as I'm unsure how.

So basically, Custom Property which can be queried on with LINQ to SQL, how?

As a follow up: this CustomProperty is NOT a column in the table. It is a separate value, but I need to both fetch it (easy enough) but also query on it!

Answer 1

Aren't you missing an equality sign there? In C#, equality is expressed with double equal signs, as in "a == b", while single equal sign signifies assignment, as in "obj.SomeProp = 5;"

Answer 2

It's called LINQ to SQL. Just to avoid misunderstandings.

About your problem: have you added that property using the designer? And have you re-created the database after that?

If you did it by hand, make sure you have a private storage field (like _CustomProperty), and your property (CustomProperty) is marked with the ColumnAttribute, e.g.

private string _CustomProperty;

[Column(Storage="_CustomProperty", CanBeNull=true)]
public string CustomProperty
{
    get { return _CustomProperty; }
}

Hope this helps.

Answer 3

As you can understand, there can't be any magic, so essentially there will be two queries: first one is a SQL query with database criteria and on its result there should be applied your custom criteria as a second query.

So the workaround you could use is to split two parts explicitly like this:

var dbFetch = (from p in db.TableName where p.RealProperty ==" value" select p).ToArray();
var result = from p in dbFetch where p.CustomProperty == "value" select p;

But of course you'll run into several limitations. For example if you fetching results page-by-page, the second criterion will break paging since it performs additional filtering.

HTH

Answer 4

I have implemented a system where you can query manually added properties that represent enumerated wrappers around integer properties from database columns. So I know it's possible, and it looks like you might be wanting to do something similar. The way I did it was not easy, though, and unless you are building a framework that you want to use properly for many cases, you might be better off using the solution suggested by archimed7592. I don't have the code handy at the moment so I can't provide all the details, but briefly my solution works like this. I created a custom LINQ provider, replacing the LINQ-to-SQL provider. I did this by implementing a custom IQueryable interface that returned my LINQ provider instead of that provided by LINQ-to-SQL. Then, in the functions that take expression objects, I pre-processed the expression before returning the result. I replaced all comparisons between enum-type properties and enum values with comparisons between integer properties and integer values, then passed that expression to the normal LINQ-to-SQL implementation in order to return the result. Since expressions are read-only, I had to make a (recursive) function that re-built the entire expression with the customized parts replaced.