none
sql to linq RRS feed

  • Question

  • Hi, I need to convert this sql query in to Linq Query, can anyone help me?


    select Trace.*,a.UserName,b.UserName as FeatureName from Trace
    left join aspnet_Users a on Trace.UserId = a.UserId
    left join aspnet_Users b on Trace.FeatureId = b.UserId
    where Trace.FeatureType = 'User'
    Sunday, December 13, 2009 10:10 AM

Answers

  • Hi Shariful Islam,

    Kristofer's excellent and concise query assumes that you have setup the navigational properties using your VS Designer. If you do not have any relations in your dbml or want to use manual join syntax then you can write:

    from t in Trace
    join a in aspnet_Users on t.UserId equals a.UserId      into t_a
    join b in aspnet_Users on t.UserId equals b.FeatureId into t_b
    where t.FeatureType == "User"
    
    from a in t_a.DefaultIfEmpty()
    from b in t_b.DefaultIfEmpty()
    select new
    { t, a.UserName, FeatureName=b.UserName }

    Also, you might want to have a look at some samples described in this post: C#: Left outer joins with LINQ

    Hope that helps.

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Tuesday, December 15, 2009 7:22 AM
  • If I got you right, you will need to interchange the columns in the join predicate. Something like:

    from t in Trace
    join a in aspnet_Users on t.UserId 	equals a.UserId into t_a
    join b in aspnet_Users on t.FeatureId 	equals b.UserId into t_b
    where t.FeatureType == "User"
    
    from a in t_a.DefaultIfEmpty()
    from b in t_b.DefaultIfEmpty()
    select new
    { t, a.UserName, FeatureName=b.UserName }
    


    Syed Mehroz Alam
    My Blog | My Articles
    Tuesday, December 15, 2009 9:22 AM

All replies

  • from t in dc.Trace
    where t.FeatureType == "User"
    select new { t, t.UsersByUserID.UserName, t.UsersByFeatureID.UserName }

    ...depending on how you generate your datacontext, the navigation properties UsersByUserID, UsersByFeatureID etc may have other names, e.g. Users1, Users2 etc.

    .
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Sunday, December 13, 2009 2:48 PM
    Answerer
  • thanx Kristofer for your answer, but i don't think it will solve my problem. i am using linq to sql.  and is it possible to join with same table multiple time in linq? just like i did in sql.
    Tuesday, December 15, 2009 3:48 AM
  • Hi Shariful Islam,

    Kristofer's excellent and concise query assumes that you have setup the navigational properties using your VS Designer. If you do not have any relations in your dbml or want to use manual join syntax then you can write:

    from t in Trace
    join a in aspnet_Users on t.UserId equals a.UserId      into t_a
    join b in aspnet_Users on t.UserId equals b.FeatureId into t_b
    where t.FeatureType == "User"
    
    from a in t_a.DefaultIfEmpty()
    from b in t_b.DefaultIfEmpty()
    select new
    { t, a.UserName, FeatureName=b.UserName }

    Also, you might want to have a look at some samples described in this post: C#: Left outer joins with LINQ

    Hope that helps.

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Tuesday, December 15, 2009 7:22 AM
  • Hi Mehroz,
    Thanks for ur answer. but i don't have the featureId column in the aspnet_users table. it is in the Trace Table. my main problem is. In trace table i have userId column and featureid column the value of userId column would be userid from aspnet_user table and the value of featureid column could be userid,departmentid,templateid etc.now i need to fetch the username for the corresponding userid column from aspnet_user table and also need to fetch the username for the corresponding featureid column from aspnet_userTable. hope u got my problem. may be i have to use subquery. thanks for ur time.
    Tuesday, December 15, 2009 8:14 AM
  • If I got you right, you will need to interchange the columns in the join predicate. Something like:

    from t in Trace
    join a in aspnet_Users on t.UserId 	equals a.UserId into t_a
    join b in aspnet_Users on t.FeatureId 	equals b.UserId into t_b
    where t.FeatureType == "User"
    
    from a in t_a.DefaultIfEmpty()
    from b in t_b.DefaultIfEmpty()
    select new
    { t, a.UserName, FeatureName=b.UserName }
    


    Syed Mehroz Alam
    My Blog | My Articles
    Tuesday, December 15, 2009 9:22 AM