none
Entity Frameworkで内部結合するには? RRS feed

  • 質問

  • 山崎(ゆ)といいます。

    SQLite3にて、3つのテーブルに対するINNER JOINを実行しようとしています(user has many events, room has many events)。

    Includeを使用した以下のクエリを実行してみました。
    var evtsq = entities.Event.Include("User").Include("Room")
    
        .Where(e => e.User.id == userId
    
            && e.ev_type > 20 && e.ev_type < 70);
    
    
    
    System.Diagnostics.Debug.WriteLine(evtsq.ToTraceString());
    
    var evts = evtsq.ToArray();
    
    
    
    

    ToTraceString()でクエリを見ると、LEFT OUTER JOINが並んでいました(長いので略しています)。
    SELECT 
    
    1 AS [C1], 
    
    [Filter1].[id1] AS [id], 
    
    // 略: eventsののカラム
    
    [Filter1].[id2] AS [id1], 
    
    [Filter1].[id3] AS [id2], 
    
    1 AS [C2], 
    
    [Filter1].[id4] AS [id3], 
    
    // 略: usersのカラム
    
    [Filter1].[room_id1] AS [room_id], 
    
    1 AS [C3], 
    
    [Filter1].[id5] AS [id4], 
    
    // 略: roomsのカラム
    
    [Join6].[id6] AS [id5]
    
    FROM   (SELECT [Extent1].[id] AS [id1], [Extent1].[created_at] AS [created_at1], [Extent1].[created_by] AS [created_by1], [Extent1].[updated_at] AS [updated_at1], [Extent1].[updated_by] AS [updated_by1], [Extent1].[occurred_at] AS [occurred_at1], [Extent1].[hd_clock_at] AS [hd_clock_at], [Extent1].[user_id] AS [user_id1], [Extent1].[room_id] AS [room_id2], [Extent1].[ev_type] AS [ev_type], [Extent1].[user_name] AS [user_name], [Extent1].[room_no] AS [room_no1], [Extent1].[stor_no] AS [stor_no1], [Extent2].[id] AS [id2], [Extent2].[created_at] AS [created_at4], [Extent2].[created_by] AS [created_by4], [Extent2].[updated_at] AS [updated_at4], [Extent2].[updated_by] AS [updated_by4], [Extent2].[occurred_at] AS [occurred_at2], [Extent2].[posted_flg] AS [posted_flg], [Extent2].[deleted] AS [deleted2], [Extent2].[event_id] AS [event_id1], [Extent2].[user_id] AS [user_id2], [Extent2].[room_id] AS [room_id3], [Extent2].[price_id] AS [price_id], [Extent2].[menu_id] AS [menu_id], [Extent2].[room_no] AS [room_no4], [Extent3].[id] AS [id3], [Extent3].[created_at] AS [created_at5], [Extent3].[created_by] AS [created_by5], [Extent3].[updated_at] AS [updated_at5], [Extent3].[updated_by] AS [updated_by5], [Extent3].[occurred_at] AS [occurred_at3], [Extent3].[process_at] AS [process_at], [Extent3].[process_by] AS [process_by], [Extent3].[event_id] AS [event_id2], [Extent3].[user_id] AS [user_id3], [Extent3].[room_id] AS [room_id4], [Extent3].[msg_no] AS [msg_no], [Extent3].[room_no] AS [room_no5], [Extent3].[stor_no] AS [stor_no2], [Extent4].[id] AS [id4], [Extent4].[created_at] AS [created_at2], [Extent4].[created_by] AS [created_by2], [Extent4].[updated_at] AS [updated_at2], [Extent4].[updated_by] AS [updated_by2], [Extent4].[deleted_at] AS [deleted_at1], [Extent4].[deleted_by] AS [deleted_by1], [Extent4].[deleted] AS [deleted1], [Extent4].[room_id] AS [room_id1], [Extent4].[room_no] AS [room_no2], [Extent4].[dorm_cd] AS [dorm_cd1], [Extent4].[reg_code] AS [reg_code1], [Extent4].[name_kanji] AS [name_kanji1], [Extent4].[name_kana] AS [name_kana1], [Extent4].[sex] AS [sex1], [Extent4].[reg_type] AS [reg_type1], [Extent4].[meal_flg] AS [meal_flg1], [Extent4].[stat_flg] AS [stat_flg1], [Extent4].[auth_flg] AS [auth_flg1], [Extent4].[op] AS [op1], [Extent4].[op_flg] AS [op_flg1], [Extent4].[note] AS [note1], [Join4].[id5], [Join4].[created_at3], [Join4].[created_by3], [Join4].[updated_at3], [Join4].[updated_by3], [Join4].[room_no3], [Join4].[room_sym], [Join4].[idm], [Join4].[edy_no], [Join4].[existence], [Join4].[note2], [Join4].[id7], [Join4].[created_at6], [Join4].[created_by6], [Join4].[updated_at6], [Join4].[updated_by6], [Join4].[deleted_at2], [Join4].[deleted_by2], [Join4].[deleted3], [Join4].[room_id5], [Join4].[room_no6], [Join4].[dorm_cd2], [Join4].[reg_code2], [Join4].[name_kanji2], [Join4].[name_kana2], [Join4].[sex2], [Join4].[reg_type2], [Join4].[meal_flg2], [Join4].[stat_flg2], [Join4].[auth_flg2], [Join4].[op2], [Join4].[op_flg2], [Join4].[note3]
    
    	FROM     [events] AS [Extent1]
    
    	LEFT OUTER JOIN [meals] AS [Extent2] ON ([Extent1].[id] = [Extent2].[event_id]) AND ([Extent2].[event_id] IS NOT NULL)
    
    	LEFT OUTER JOIN [messages] AS [Extent3] ON ([Extent1].[id] = [Extent3].[event_id]) AND ([Extent3].[event_id] IS NOT NULL)
    
    	LEFT OUTER JOIN [users] AS [Extent4] ON [Extent1].[user_id] = [Extent4].[id]
    
    	LEFT OUTER JOIN  (SELECT [Extent5].[id] AS [id5], [Extent5].[created_at] AS [created_at3], [Extent5].[created_by] AS [created_by3], [Extent5].[updated_at] AS [updated_at3], [Extent5].[updated_by] AS [updated_by3], [Extent5].[room_no] AS [room_no3], [Extent5].[room_sym] AS [room_sym], [Extent5].[idm] AS [idm], [Extent5].[edy_no] AS [edy_no], [Extent5].[existence] AS [existence], [Extent5].[note] AS [note2], [Extent6].[id] AS [id7], [Extent6].[created_at] AS [created_at6], [Extent6].[created_by] AS [created_by6], [Extent6].[updated_at] AS [updated_at6], [Extent6].[updated_by] AS [updated_by6], [Extent6].[deleted_at] AS [deleted_at2], [Extent6].[deleted_by] AS [deleted_by2], [Extent6].[deleted] AS [deleted3], [Extent6].[room_id] AS [room_id5], [Extent6].[room_no] AS [room_no6], [Extent6].[dorm_cd] AS [dorm_cd2], [Extent6].[reg_code] AS [reg_code2], [Extent6].[name_kanji] AS [name_kanji2], [Extent6].[name_kana] AS [name_kana2], [Extent6].[sex] AS [sex2], [Extent6].[reg_type] AS [reg_type2], [Extent6].[meal_flg] AS [meal_flg2], [Extent6].[stat_flg] AS [stat_flg2], [Extent6].[auth_flg] AS [auth_flg2], [Extent6].[op] AS [op2], [Extent6].[op_flg] AS [op_flg2], [Extent6].[note] AS [note3]
    
    		FROM  [rooms] AS [Extent5]
    
    		LEFT OUTER JOIN [users] AS [Extent6] ON ([Extent5].[id] = [Extent6].[room_id]) AND ([Extent6].[room_id] IS NOT NULL) ) AS [Join4] ON [Extent1].[room_id] = [Join4].[id5]
    
    	WHERE ([Extent1].[ev_type] > 20) AND ([Extent1].[ev_type] < 70) ) AS [Filter1]
    
    LEFT OUTER JOIN  (SELECT [Extent7].[id] AS [id8], [Extent7].[created_at] AS [created_at7], [Extent7].[created_by] AS [created_by7], [Extent7].[updated_at] AS [updated_at7], [Extent7].[updated_by] AS [updated_by7], [Extent7].[room_no] AS [room_no7], [Extent7].[room_sym] AS [room_sym], [Extent7].[idm] AS [idm], [Extent7].[edy_no] AS [edy_no], [Extent7].[existence] AS [existence], [Extent7].[note] AS [note4], [Extent8].[id] AS [id6], [Extent8].[created_at] AS [created_at8], [Extent8].[created_by] AS [created_by8], [Extent8].[updated_at] AS [updated_at8], [Extent8].[updated_by] AS [updated_by8], [Extent8].[deleted_at] AS [deleted_at], [Extent8].[deleted_by] AS [deleted_by], [Extent8].[deleted] AS [deleted], [Extent8].[room_id] AS [room_id], [Extent8].[room_no] AS [room_no8], [Extent8].[dorm_cd] AS [dorm_cd], [Extent8].[reg_code] AS [reg_code], [Extent8].[name_kanji] AS [name_kanji], [Extent8].[name_kana] AS [name_kana], [Extent8].[sex] AS [sex], [Extent8].[reg_type] AS [reg_type], [Extent8].[meal_flg] AS [meal_flg], [Extent8].[stat_flg] AS [stat_flg], [Extent8].[auth_flg] AS [auth_flg], [Extent8].[op] AS [op], [Extent8].[op_flg] AS [op_flg], [Extent8].[note] AS [note5]
    
    	FROM  [rooms] AS [Extent7]
    
    	LEFT OUTER JOIN [users] AS [Extent8] ON ([Extent8].[room_id] IS NOT NULL) AND ([Extent7].[id] = [Extent8].[room_id]) ) AS [Join6] ON [Filter1].[room_id2] = [Join6].[id8]
    
    WHERE [Filter1].[user_id1] = @p__linq__5
    
    
    
    


    発行したいSQL文は、以下の文で、本家のsqlite3.exeで実行すると2倍以上速いです。
    SELECT * FROM events e, users u, rooms r
    WHERE e.user_id = 261 AND e.room_id = r.id AND ev_type > 20 AND ev_type < 70

    EFでも同じSQLが発行できればほぼ同じ速度が出るのではないかと思っているのですが、EFでリレーションを設定した場合、エンティティのプロパティから、外部キーが参照できなくなってしまうので、たとえば、こんな風には書くことができません。

    どのように書いたらよいのでしょうか?

    var evts = from evt in entities.Event
        join u on evt.user_id equals u.id
        join r on evt.room_id equals r.id
        where evt.ev_type > 20 && evt.ev_type < 70
        select evt;
    • 編集済み yyamasak 2009年11月9日 12:13 最後の説明を追加
    2009年11月9日 12:05