none
Help converting t-sql query into EF's method syntax RRS feed

  • Question

  • Hi,

    What would be an EF method syntax equivalent for the following TSQL query?

    select istb.service_id, ss.service_desc, selected=1
    from istb_services istb
    inner join setup_services ss on istb.service_id=ss.service_id
    where istb.istb_id=3
    union
    select ss.service_id, ss.service_desc, selected=0
    from setup_services ss
    where ss.service_id not in (select service_id from istb_services where istb_id=3)

    I tried using

                    var _existing = context.istb_services.Where(e => e.istb_id == IstbID);
                    var _others = context.setup_services.Except(_existing);
    

    but it is generating compile-time error:

    The best overloaded method match for 'System.Data.Objects.ObjectQuery<BIS_Modal.setup_services>.Except(System.Data.Objects.ObjectQuery<BIS_Modal.setup_services>)' has some invalid arguments

    I understand I can't pass different type of ObjectQuery to the .Except method but then what would be the alternative code?


    Paradise lies at the feet of thy mother. - Prophet Mohammed (PBUH) (skype: ali.net.pk)

    Monday, May 28, 2012 7:37 AM

Answers

  • Hi Ali .NET,

    Welcome to MSDN Forum.

    Please refer to the code below.

    var _existing = (from i in context.istb_services where i.istb_id == 3 select i.service_id).ToList();
    
    var queryOne = (from i in context.istb_services join s in context.setup_services on i.service_id equals s.service_id where i.istb_id == 3 select new { s.service_id, s.service_desc }).ToList();                
    
    var queryTwo = (from s in context.setup_services where (!_existing.Contains(s.service_id)) select new { s.service_id, s.service_desc }).ToList();
    
    var query = (queryOne.Union(queryTwo)).ToList();

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Khadim Ali Wednesday, May 30, 2012 2:21 PM
    Tuesday, May 29, 2012 4:58 AM
    Moderator