Entity frameworkのLINQで関数を使いたい

Question

前提

◎勉強のため、DBにある顧客情報を、一覧表示させるアプリを作っています。

◎顧客情報に関する検索条件は60項目以上あり、
検索値があればwhere句を追加していくクエリを書いています。
（Entity framework使用）

◎DBには日付を扱うカラムもありますが、
SQLiteを使用しているため、TEXT型でデータを格納しています。

実現したいこと

①DBにある日付データ(TEXT型)をDateTime型に変換し、where句で比較したいのですが、
クエリ内でDateTime.Parse(日付データ)をしようとすると、エラーが発生します。

②DBにある日付データ(TEXT型)が、DateTime型に変換できるものなのかも、
どこかで検証せねばなりません。

そもそも日付データの扱い方が間違っているのでしょうか。
どうか宜しくお願い致します。

###環境
◎.net Frame Work 4.61
◎C#
◎Entity framework 6.2
◎SQLite3
◎visual studio Community 2017
◎windows7 64bit

エラーメッセージ

System.NotSupportedException: 'メソッド 'System.DateTime Parse(System.String)' は LINQ to Entities では認識されないため、ストア式に変換できません。'

ソースコード

※Entityモデル
public partial class MT_Customers
{
    [Key]
    public int? 顧客ID { get; set; }

    public string 入会日時 { get; set; }
}

//検索項目を定義
public class MyFilters
{
    public int? 顧客ID { get; set; }
    public DateTime? 入会日時 { get; set; }
}

//検索値を指定 (サンプル)
MyFilters filters = new MyFilters();
filters.顧客ID = 3451;
filters.入会日時 = DateTime.Now.AddYears(1);

//DBから顧客情報を取得する
using (var context = new EntitySQLite())
{
	var list =
	    (from cus in context.MT_Customers
	     where cus.世帯主フラグ == 1
	     orderby cus.顧客名かな
	     select cus
	     ).AsQueryable();

	if (filters.顧客ID.HasValue) list = list.Where(x => x.顧客ID == filters.顧客ID);

	if (filters.入会日時.HasValue) list = list.Where(x => DateTime.Parse(x.入会日時) >= filters.入会日時);
	//ここでエラー発生

	DataGrid1.ItemSource = list.ToList<Customers>();
}

Answer 1

クエリは最終的には SQL になりますが、対応していないものがあるとエラーになります。
.ToArray() として SQL を発行し、改めて LINQ to Object で検索するという手もありますがパフォーマンスが劣化するため、データがテキストであるならば検索項目のほうを文字列にしたらいいのではないかと思います。

Comments

[elvis]

早速のご回答ありがとうございます。
大変勉強になります。

＞データがテキストであるならば検索項目のほうを文字列にしたらいいのではないかと思います。
「○月○日以降」という検索を、文字列同士でする場合は、どのように記述すればよいのでしょうか。

[x_x]

わたしにはどのような形式で格納しているかわからないので、あらかじめ決められたフォーマットに沿って変換するということになるでしょう

[elvis]

DBには「yyyy/MM/dd」でTEXT型として格納してます。
これに対して、「.Where(x => x.入会日時 >= filters.入会日時);」
のように、>=を用いることは当然できないので、変換が必要になるかと思います。
しかし仰られるとおり、DateTime.Parth（）とすればエラーになります。
どこでどのように変換すればよいのでしょうか。質問ばかりですみません。

[x_x]

filters.入会日時が検索項目ならstringにして
filters.入会日時 = dt.ToString("yyyy/MM/dd");

[elvis]

例えば、入会日時が○月○日～○月○日のレコードを返したい場合は、DateTime型でないと難しいでしょうか？

Answer 2

クラス定義 public　class MyFilters() の () とかその中の変数の定義の Nullable が意味不明ですが、それはちょっと置いといて・・・

クエリ内でDateTime.Parse(日付データ)をしようとすると、エラーが発生します。

と言うとことだけ解決するなら、そこでパースしないで済むよう、例えば list を List<MyFilters> などに以下のように「cus.入会日時」をパースして取得し、

var list =
        (from cus in context.MT_Customers
         where cus.世帯主フラグ == 1
         orderby cus.顧客名かな
         select new MyFilters
         {
             顧客ID = cus.顧客ID,
             入会日時 = DateTime.Parse(cus.入会日時)
         }).ToList();

以下のようにするということではどうでしょう？

list = list.Where(x => x.入会日時 >= filters.入会日時);

試してないのでハズレだったらすみません。

【追記】

下の 2019/02/08 23:11 の私のコメントで「ご参考に、試したコードを回答欄にアップしておきます」と書きましたが、それを以下に書いておきます。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;

namespace ConsoleApplication1
{
    public class Customers
    {
        public int Id { get; set; }
        public string Date { get; set; }
    }

    public class Filter
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            IEnumerable<Customers> customers = new List<Customers>
            {
                new Customers { Id = 1, Date = "2019/1/1" },
                new Customers { Id = 2, Date = "2019/1/2" },
                new Customers { Id = 3, Date = "2019/1/3" }
            };

            var list = (from c in customers
                        select new Filter
                        {
                            Id = c.Id,
                            Date = DateTime.Parse(c.Date)
                        }).AsQueryable();

            foreach(Filter f in list)
            {
                Console.WriteLine($"Id: {f.Id}, Date: {f.Date}");
            }

            // 結果は：
            // Id: 1, Date: 2019/01/01 0:00:00
            // Id: 2, Date: 2019/01/02 0:00:00
            // Id: 3, Date: 2019/01/03 0:00:00

            var list2 = (from c in customers
                         select c).AsQueryable();

            var filter = new Filter { Id = 1, Date = new DateTime(2019, 1, 2) };
            list2 = list2.Where(x => DateTime.Parse(x.Date) >= filter.Date);

            foreach (Customers c in list2)
            {
                Console.WriteLine($"Id: {c.Id}, Date: {c.Date}");
            }

            // 結果は：
            // Id: 2, Date: 2019/1/2
            // Id: 3, Date: 2019/1/3
        }
    }
}

【追記２】

x_x さんが言われる通り、Linq to Object では問題ないが Linq to Entities では SQL に変換できないのでダメということだったようです。

LINQにも色々 ～SQLに変換されるモノと変換されないモノ
https://codezine.jp/article/detail/8474

なので、SQLite3 で DataTime 型を使えるならそれを使うのが正解のようです。

ただし、DB に NULL があると、SQL に変換されるということなので、期待通りの結果にならないかもしれません。

Comments

[elvis]

()の部分、修正しました。
ありがとうございます。

ご提案いただいたコードを実行すると、以下のエラーが発生しました。

System.NotSupportedException: 'メソッド 'System.DateTime Parse(System.String)' は LINQ to Entities では認識されないため、ストア式に変換できません。'

[退会済みユーザー]

ダメだったそうで、すみません。

SQLite では試せないのですが、似たようなコードで試してみましたけど NotSupportedException は出ません。問題が再現できないので何が原因なのか分かりません。

ご参考に、試したコードを回答欄にアップしておきます。

[退会済みユーザー]

x_x さんが言われる通り、Linq to Object では問題ないが Linq to Entities では SQL に変換できないのでダメということだったようです。上の【追記２】を見てください。

[elvis]

ご丁寧にサンプルコードまでご提示いただき、有難うございます。
お陰様でとてもわかり易く理解することができました。
感謝申し上げます。また宜しくお願いしますm(_ _)m

Answer 3

こんにちは。

SQLiteのEFプロバイダがサポートしているかどうかはわかりませんが、DateTime.Parseでさらっと変換できるTEXT形式で保管しているのであれば、MT_Customersの入会日時の型をDateTimeにしてもそのまま動きそうな気がしますが、どうでしょう？

Comments

[elvis]

tomato様、いつもご丁寧に教えてくださり、ありがとうございます。

MT_Customersの入会日時の型をDateTime型にしたら、以下のエラーが出ました。

System.InvalidOperationException: ''MT_Customers' の '入会日時' プロパティを 'System.String' 値に設定できませんでした。このプロパティは、'System.DateTime' 型の NULL 以外の値に設定する必要があります。'

[tamoto]

あー、たぶんテーブルのマッピング情報の方も書き換えないといけないですね……とはいえ、明らかに罠が多そうな方法なのでやめといたほうがいいかもです、忘れてください。
一応、EntityFramework6+SQLiteでもDateTimeのマッピングには対応しているようでした。

[elvis]

SQLiteの入会日時カラムを「DATETIME」にして、MT_Customersの入会日時の型もDateTime型にしたら、動きました！

SQLiteにはTEXT型やINTEGER型などしかなく、日付方を扱うにはSQLiteで用意されたdatetime()関数などを使用するしかないと思っておりましたが、SQLite3からDATETIME型を含む様々な型が追加されたようです。

https://goo.gl/UgYqUa
https://goo.gl/tp44q7

これで、一安心、してもよいのでしょうか？

[tamoto]

なるほど、SQLite側のカラムも変更可能な要件だったのですね。
SQLiteにはDATETIME型そのものは存在せず、日付時刻フォーマットで記述された文字列データをDateTimeとして扱うようになっていたはずです。
なので、その設定で意図した動作がちゃんと実現できているなら、多分今後も動くと思います。
過去データなどがある場合は、EFでInsertしたときとデータ形式に違いがないかは直接確認しておいた方が良いです。

[elvis]

ありがとうございます。しっかりテストしたいと思います。
親身にお答えいただき、ありがとうございました。
完動中です◎