none
Need SQL to LINQ Convert RRS feed

  • Question

  • Hi,

    Could someone convert the following SQL to LINQ:
    select * from A _a, B _b
         where A.xx = B.xx    and not exists     
                  (select * from C _c     
                     where  _a.zz = _c.zz and
                     _a.yy    = _c.yy ) 

    Thanks in advance,
    Amino
    Friday, March 12, 2010 8:54 AM

Answers

  • Hi,

    Here's something to start with:
    from _a in dc.A
    join _b in dc.B on _a.xx equals _b.xx
    where  !dc.C.Any ( _c => _a.zz == c.zz and _a.yy == _c.yy )
    select new { _a, _b }
    


    Notice that for Select *, we can create an anonymous type containing the fields from tables.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Friday, March 12, 2010 9:15 AM

All replies

  • Hi,

    Here's something to start with:
    from _a in dc.A
    join _b in dc.B on _a.xx equals _b.xx
    where  !dc.C.Any ( _c => _a.zz == c.zz and _a.yy == _c.yy )
    select new { _a, _b }
    


    Notice that for Select *, we can create an anonymous type containing the fields from tables.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Friday, March 12, 2010 9:15 AM
  • Thanks for your reply, but it really is not direct convertion of the above SQL.

    Funny thing is that moderator is marking your answer as answered one, even if this is not the answer of what I have asked for.
    Thursday, March 18, 2010 10:32 AM
  • Hi,

    The LINQ query I posted was supposed to serve as a starting point in achieving a result as you described in your original question. Notice that LINQ is just another way of expressing our set based queries and we can not find an exact match for a TSQL query in certain cases. However, in most of the cases, we can develop a LINQ query that may produce the same result as a TSQL one. Can you explain why such a query could not be used in your situation?

    Also, the moderators here wait for a user to respond to the replies on his/her post and mark any post as answer but when there is no reply from user, they try to pick up the best reply as the answer. This way, others who happen to discover a post through searching can quickly find the answer for a similar situation. If you don't think this is the answer to your question, you can always click "Unmark As Answer" to undo this.

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, March 18, 2010 10:54 AM
  • First of all thank you for explaining the idea behind moderator click zone:)

    I tried to convert what you have written to vb.net and compiled it, when running that application, it just doing nothing and keeps loading.

    Is this converions of your code correct:
    from _a in dc.A
    join _b in dc.B on _a.xx equals _b.xx
    where not dc.C.Any(function(_c as dc.C) _a.zz = c.zz and _a.yy = _c.yy)  select new with { _a, _b }
    


    At least I am not getting any compiler error.

    Thanks alot for you help I really appreciate it.
    BR,
    Amino
    Thursday, March 18, 2010 11:32 AM
  • I am not a VB expert but the conversion seems good. Can you see what TSQL query is generated ?

    Also, in case the query runs but returns the wrong results, can you provide some test data with the actual and expected output?

    Also, these are some great LINQ examples using VB: LINQ To SQL Samples VB

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Friday, March 19, 2010 6:18 AM
  • Hello Mr. Syed I got it working. Thank you alot for your help. I had a mistake in my database schemas.

     

    Your are king:)

     

    BR,

    Amino

    Friday, March 19, 2010 7:10 AM
  • I am not a VB expert but the conversion seems good. Can you see what TSQL query is generated?

    Also, in case the query runs but returns the wrong results, can you provide some test data with the actual and expected output?

    Also, these are some great LINQ examples using VB: LINQ To SQL Samples VB

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles

    I have the same problem. If only there is a conversion tool for it now.
    Tuesday, August 3, 2010 11:12 AM