[c#]左外部联接多个语句

标签: sql Linq SQLServer C#
发布时间: 2017/2/26 14:35:43
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我一直在这几个小时现在。这是我试过︰

List<SoccerTeamsModel> incompleteFootballTeams = (from fbt in db.footballTeams
    join fbct in db.footballCustomTeams
    on fbt.team_id equals fbct.team_id && fbct.client_id == clientId && fbct.language == selectedLanguage into t
    from ct in t.DefaultIfEmpty()
    where fbt.league_id == leagueId
    orderby fbt.name ascending
    select new SoccerTeamsModel
    {
        TeamId = fbt.team_id,
        ClientId = ct.client_id, 
        etc...
    }).ToList();

我试图复制 SQL 查询︰

  SELECT *
  FROM [SoccerData].[dbo].[footballTeams] AS fbt
  LEFT OUTER JOIN [SoccerData].[dbo].[footballCustomTeams] AS fbct
  ON fbt.team_id = fbct.team_id AND fbct.client_id = 104 AND fbct.[language] = 'fr-FR'  
  WHERE fbt.league_id = 8
  ORDER BY fbt.name ASC

什么搞乱我起来似乎正试图把放在"on"条款的所有多。我见过一些例子,但它们不包括左外部联接。我似乎无法把这两个放在一起。

解决方法 1:

我认为此查询应提供类似于你的 SQL 表达式。它是相当不同的从你的 LINQ,但我假设的 SQL 结果是你的目标︰

var clientId = 104;
var lang = "fr-FR";
var leagueId = 8;

// Find teams in specifiec league
// Equivalent to this step in SQL: WHERE fbt.league_id = 8
var leagueTeams = footballTeams.Where(fbt => fbt.league_id == leagueId).ToList();

// Find teams that fulfill required conditions
// Equivalent to the On condition in SQL
var teams = footballCustomTeams
.Where(fbct => leagueTeams.Any(fbt => fbct.team_id == fbt.team_id) &&
                fbct.client_id == clientId &&
                fbct.language == lang)
.Select(fbct => new { TeamId = fbct.team_id, ClientId = fbct.client_id });

而不是复制我简单地划分 LINQ 联接查询到更 subqueries,每个缩小的结果。可能需要稍微调整,以及不会接触到您的模型只是试图推断,从样品。

赞助商