none
can anyone convert the below Linq query to Sql RRS feed

  • Question

  • Hi am new to Linq to sql could any one convert the  below linq query

    from

     

    GS in

    abtMetricContext.Groups

     

    join GD in

    abtMetricContext.Groups

     

    on GS.MemberId equals

    GD.ParentMemberId

     

    join MM in

    abtMetricContext.MembersMetrics

     

    on 1 equals

    1

     

    join M in

    abtMetricContext.Metrics

     

    on MM.MetricId equals

    M.MetricId

     

    where

    (MM.MemberId == GS.MemberId || MM.MemberId == GD.MemberId)

    && GS.MemberId == objSearchParamRequest.CurrentUserHierarchy.Descendant.Descendant.MemberID.Value

    && M.MetricCategoryId == (

    int)MetricCategory

    .LeafMetric

    && M.DurationTypeId == durationTypeID

    && relatedMembers.Contains(M.RelatedMemberId.Value)

    && M.IsDeleted ==

    false

     

    orderby

    M.MetricName

     

    select new DTO.

    Metrics

    {

    MetricID = M.MetricId,

    MetricName = M.MetricName

    }).Distinct().ToList();

    Thursday, June 2, 2011 12:52 PM

Answers

  • Hi,

     

    If you can run this query in your environment then the easiest way is to use the DataContext Log.

    To turn it on just set the Log property of the DataContext like here:

    db.Log = Console.Out;
    
    

    After this the query will be written out to the output.


    Regards, Peter //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, June 2, 2011 1:06 PM
  • The easiest way to get the exact SQL query generated would be to use the DataContext.Log property. There's an example which writes all of the SQL queries generated by LINQ to SQL to the debug window here.

    It's possible to come up with a close guess, but whatever we come up with may not really be exactly what LINQ generates.

    Thursday, June 2, 2011 1:28 PM
  • Hi,

    Or you can use the

    dc.GetCommand(query).CommandText

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx

     


    Regards, Peter //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, June 2, 2011 1:27 PM

All replies

  • from

     

    GS in

    abtMetricContext.Groups

     

    join GD in

    abtMetricContext.Groups

     

    on GS.MemberId equals

    GD.ParentMemberId

     

    join MM in

    abtMetricContext.MembersMetrics

     

    on 1 equals

    1

     

    join M in

    abtMetricContext.Metrics

     

    on MM.MetricId equals

    M.MetricId

     

    where

    (MM.MemberId == GS.MemberId || MM.MemberId == GD.MemberId)

    && GS.MemberId == '' && M.MetricCategoryId == ''

    && M.DurationTypeId == ''&& relatedMembers.Contains(M.RelatedMemberId.Value)

    && M.IsDeleted ==

    false

     

    orderby

    M.MetricName

     

    select new DTO.

    Metrics

    {

    MetricID = M.MetricId,

    MetricName = M.MetricName

    }).Distinct().ToList();

    • Merged by Leo Liu - MSFT Friday, June 3, 2011 7:52 AM Totally the same.
    Thursday, June 2, 2011 12:50 PM
  • Hi,

     

    If you can run this query in your environment then the easiest way is to use the DataContext Log.

    To turn it on just set the Log property of the DataContext like here:

    db.Log = Console.Out;
    
    

    After this the query will be written out to the output.


    Regards, Peter //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, June 2, 2011 1:06 PM
  • Hi,

    Try with LinqPad:

    http://www.linqpad.net/

    It could help you in many other cases/ways.

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    Thursday, June 2, 2011 1:24 PM
  • Hi,

    Or you can use the

    dc.GetCommand(query).CommandText

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx

     


    Regards, Peter //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, June 2, 2011 1:27 PM
  • The easiest way to get the exact SQL query generated would be to use the DataContext.Log property. There's an example which writes all of the SQL queries generated by LINQ to SQL to the debug window here.

    It's possible to come up with a close guess, but whatever we come up with may not really be exactly what LINQ generates.

    Thursday, June 2, 2011 1:28 PM
  • Hello Thirsty Brain,

    Thanks for your post.

    I have noticed that the members have given you some sugguetions, and most of them are correct. Have you tried something and resolved your issue? Could you please show us if you have any update?

     

    Good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 6, 2011 7:06 AM
    Moderator