Converting string type to DateTime in EntityFramework query

Question

My Date field in database is saved in nvarchar(10).Here I want compaire two date together This is my code:

public bool SalesFactorIsExist(IFeeKindDTO inFeeKindDTO)
{
    bool isExist = false;

    int count = 0;
    var context = new SabzNegar01Entities1();
    try
    {
        count = (from p in context.tbl_SalesFactor_D
                 where p.tbl_SalesFactor_M.tbl_Customer.CustomerGroupCode ==   inFeeKindDTO.CustomerGroupCode && p.StockCode == inFeeKindDTO.StockCode && ConvertStringDate(p.tbl_SalesFactor_M.FactorSalesDate) >=  ConvertStringDate(inFeeKindDTO.StartDate)
                 select new { p.Row }).Count();
        if (count != 0)
        {
            isExist = true;
        }
}
catch (Exception ex)
{
    PMessageBox.Show("خطا در خواندن اطلاعات", "اخطار", PMessageBoxButtons.Ok,   PMessageBoxIcons.Error);
}

    return isExist;
}

I have used from this method:

private DateTime ConvertStringDate(string inDate)
{
    DateTime result = DateTime.Parse(inDate);
    return result;
}

But there is an error:

LINQ to Entities does not recognize the method 'System.DateTime ConvertStringDate(System.String)' method, and this method cannot be translated into a store expression.

What should I do? Is there another way?

What is the error text?

Maess
– Maess

2013-12-18 14:16:51 +00:00
Commented Dec 18, 2013 at 14:16 — Maess
– Maess, Commented Dec 18, 2013 at 14:16

Maess · Accepted Answer · 2013-12-18 14:18:00Z

3

You can't call the ConvertStringDate function from an entity query.

Pull the initial list down to the server and then apply your function.

var list = context.tbl_SalesFactor_D.Where(p=> p.tbl_SalesFactor_M.tbl_Customer.CustomerGroupCode == inFeeKindDTO.CustomerGroupCode && p.StockCode == inFeeKindDTO.StockCode).ToList();

var count = list.Where(p=> ConvertStringDate(p.tbl_SalesFactor_M.FactorSalesDate) >= ConvertStringDate(inFeeKindDTO.StartDate)).Count();

answered Dec 18, 2013 at 14:18

Maess

4,16623 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sergey Berezovskiy Over a year ago

@Alalesa keep in mind, that this solution will download ALL data from tbl_SalesFactor (which satisfy first two conditions), map those data to your entities, and then filter results on client. If it doesn't hit performance or performance is not necessary, then this solution is good. Otherwise consider to change type of your date columns to hold date data instead of strings

Maess Over a year ago

Yes, a better solution would be to use the DateTime data type in your database.

Collectives™ on Stack Overflow

Converting string type to DateTime in EntityFramework query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related