I have an entity framework's EDMX generated class.
public partial class Contact : EntityBase
{
public Contact()
{
}
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public string NickName { get; set; }
public Nullable<System.DateTime> DOB { get; set; }
}
I've a LINQ expression for searching a text against all above properties.
Expression<Func<Contact, bool>> cntExpression = p => p.LastName.ToLower().Trim().Contains(searchedText) ||
p.FirstName.ToLower().Trim().Contains(searchedText) ||
p.MiddleName.ToLower().Trim().Contains(searchedText) ||
p.NickName.ToLower().Trim().Contains(searchedText) ||
(p.DOB.HasValue && p.DOB.Value.ToShortDateString().ToLower().Trim().Contains(searchedText));
'searchedText' is a variable containing the text value which has to be matched.
I now used business logic class's method to filter records using the above LINQ expression.
IQueryable<Contact> qryContact = _cntMgr.GetFiltered(cntExpression);
Till here the code gets executed, but it throws error while looping through Contact records existing in the above IQueryable result.
foreach (var contact in qryContact)
{
if (contact!=null)
{
// Some code executes here..
}
}
The error I get is this:
LINQ to Entities does not recognize the method 'System.String ToString(System.Object)' method, and this method cannot be translated into a store expression.
I already know that System.String ToString is not supported in Linq to Entities, but I need a solution or a workaround for this issue.
Please help me in getting it fixed.
Thanks in advance.
Your problem is related to the ToShortDateString
call you have. One way to get rid of it would be to inspect the searchText for a potential date string before creating the expression and work with the date instead.
Expression<Func<Contact, bool>> cntExpression;
var searchDate = default(DateTime);
if (DateTime.TryParse(searchText.Trim(), out searchDate))
{
cntExpression = p => p.DOB.HasValue && p.DOB == searchDate;
}
else
{
cntExpression = p => p.LastName.ToLower().Trim().Contains(searchedText) ||
p.FirstName.ToLower().Trim().Contains(searchedText) ||
p.MiddleName.ToLower().Trim().Contains(searchedText) ||
p.NickName.ToLower().Trim().Contains(searchedText);
}
I solved this issue by changing my expression to:
Expression<Func<Contact, bool>> cntExpression = p => p.LastName.ToLower().Trim().Contains(searchedText) ||
p.FirstName.ToLower().Trim().Contains(searchedText) ||
p.MiddleName.ToLower().Trim().Contains(searchedText) ||
p.NickName.ToLower().Trim().Contains(searchedText) ||
p.DriversLicenseNumber.ToLower().Trim().Contains(searchedText) ||
(p.FirstName + " " + p.LastName).ToLower().Trim().Contains(searchedText);
var searchDate = default(DateTime);
if (DateTime.TryParse(searchedText.Trim(), out searchDate))
cntExpression = cntExpression.And(p => p.DOB.HasValue && p.DOB == searchDate);
No error occurs now and code is exucuting properly.