如何从表1和匹配列的所有列从表2使用LINQ（LEFT OUTER JOIN） [英] How to retrieve all columns from table1 and matching columns from table2(Left outer join) using Linq

问题描述

我要检索所有从表2表1和匹配列的列。我有一个存储过程为：

I have to retrieve all the columns from table1 and matching columns from table2. I have a stored procedure as :

alter  Procedure [dbo].[usp_Property]
@UserId bigint =null
As
Begin
select P.PID, P.PropertyName, P.SBUArea, P.ListedOn, 
       P.Availability, P.Price,   F.UserID, F.PID as FavProjId  
       from dbo.Property P left outer join dbo.Favorite F
       on (F.PID=P.PID And F.UserID=@UserId)

我要得到相同的Linq查询。到目前为止，我尝试过的东西，如

I want to get Linq query for the same. So far I tried with something like

//User Id comes from session..
//var userId
var result=(from p in Properties
                   join f in Favorites
                   on p.PID equals f.PID into r
                   from r1 in r.DefaultIfEmpty()
                   where r1.UserID==userId
                   select new
                   {
                     p.PID,
                      p.PropertyName,                       
                      p.SBUArea, p.ListedOn,
                      r1.UserId
                   });

任何人都可以请大家指正。我想使用左外连接或这里的任何其他替代的东西。

Can anyone please correct me. I want to use left outer join or any other alternate thing here.

推荐答案

如果我美化你的SP代码，我得到这个：

If I beautify your SP's code, I get this:

DECLARE @UserId int 
SET @UserId = 12435 

SELECT 
     P.PID
    ,P.PropertyName
    ,P.SBUArea
    ,P.ListedOn 
    ,P.Availability
    ,P.Price
    ,F.UserID
    ,F.PID AS FavProjId  
FROM Property AS P 
LEFT JOIN Favorite AS F
    ON (F.PID=P.PID AND F.UserID = @UserId)

现在我不知道你是否需要一个用户ID的SQL语句的WHERE子句中，还是真的在加入。

Now I wonder if you need that UserId in the WHERE clause of the SQL, or really in the join.

但无论如何，这里正是SQL的LINQ当量：

But anyway, here the LINQ-equivalent of exactly that SQL:

System.Int64 __UserId = 12435;

var query = (
    from P in Repo.Property
    from F in Repo.Favorite
         .Where(fav=> fav.PID == P.PID && fav.UserID == __UserId)
         .DefaultIfEmpty() // <== makes join left join
    select new
    {
         PID = P.PID
        ,PropertyName = P.PropertyName
        ,SBUArea = P.SBUArea
        ,ListenOn = P.ListedOn 
        ,Availabiity = P.Availability
        ,Price = P.Price
        ,UserId = F.UserID
        ,FavProjId = F.PID 
    }

);

var data = (query).ToList();

这篇关于如何从表1和匹配列的所有列从表2使用LINQ（LEFT OUTER JOIN）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！