Linq Expression to Select a Field

I have a very specific LINQ query. I would like to check the existence of a randomly generated key in a table开发者_开发知识库.

The standard query could be defined as Select * from Products where SaleId == 'XXXXXXX'. In this query the XXXXXX is generated by a random character generator (a length is also provided). I created the following LINQ extension:

public static string GetUniqueId<T, TProperty>(this IEnumerable<T> source, int length, Func<T, TProperty> idProperty)
{
    bool isUnique = false;
    string uniqueId = String.Empty;
    while (!isUnique)
    {
        uniqueId = PasswordGenerator.GenerateNoSpecialCharacters(length);
        if (!String.IsNullOrEmpty(uniqueId))
        {
            isUnique = source.AsQueryable().SingleOrDefault(i => idProperty(i).Equals(uniqueId)) == null;
        }
    }
    return uniqueId;
}

However, I have noticed that this method first selects all the records from the table that is passed as a source and then runs the Where clause. This behavior is obviously very time consuming. So basically it does SELECT * FROM Products and then runs the SingleOrDefault

Is there any way I could directly run the query such that it does Select * from Products WHERE Id = 'XXXXXXX'

Here's an example of how I call it:

string id = c.L2SOnlineCountMasters.GetUniqueId(9, x => x.MID);

In this case L2SOnlineCountMasters is a table in the databse and c is the DataContext instance.

---

After reading both the comments, I realized that IQueryable should be used. However, "Equals" in Expression Call doesn't work as it throws the following error: "More than one method 'Equals' on type 'System.String' is compatible with the supplied arguments." Thus I modified the code a little bit as follows:

public static string GetUniqueId<T, TProperty>(this IQueryable<T> source, int length, Expression<Func<T, TProperty>> idProperty)
    {
        bool isUnique = false;
        string uniqueId = String.Empty;
        while (!isUnique)
        {
            uniqueId = PasswordGenerator.GenerateNoSpecialCharacters(length);
            if (!String.IsNullOrEmpty(uniqueId))
            {
                var expr = Expression.Lambda<Func<T, bool>>(
                    Expression.Call(idProperty.Body, typeof(string).GetMethod("Equals", new[] { typeof(string) }), Expression.Constant(uniqueId)), idProperty.Parameters);
                isUnique = source.SingleOrDefault(expr) == null;
            }
        }

        return uniqueId;
    }

That really solved the issue.

---

The LINQ-to-SQL engine cannot know what the Func<T, TProperty> does.

You need to accept an Expression<Func<T, TProperty>>, then splice the expression into an expression which calls .Equals.
The code would look something like

Expression.Lambda<Func<T, TProperty>>(
    Expression.Call(idProperty.Body, "Equals", new Type[0], 
                    Expresion.Constant(uniqueId)),
    idProperty.Parameters
)

Also, you should change your method to take an IQueryable<T>.

---

Indeed all records will be retrieved if you invoke c.L2SOnlineCountMasters casting to IEnumerable, what if you try this:

    public static string GetUniqueId<T, TProperty>(this IQueryable<T> source, int length, Func<T, TProperty> idProperty)
{         
bool isUnique = false;         
string uniqueId = String.Empty;         
while (!isUnique) 
{             
uniqueId = PasswordGenerator.GenerateNoSpecialCharacters(length);             
if (!String.IsNullOrEmpty(uniqueId)) 
{ 
isUnique = source.SingleOrDefault(i => idProperty(i).Equals(uniqueId)) == null;
}
}
return uniqueId;
}