EF LInq左外联合排列拳头 [英] EF LInq Left outer join sorted taking fist
问题描述
我有这个左外连接查询
var queryString = (from entity in _dbContext.Links
from translations in _dbContext.LinksTrs.Where(p => p.LinkId == entity.Id).OrderBy(p => p.LanguageCode).Take(1).DefaultIfEmpty()
select new LinksAdminListModel()
{
Id = entity.Id,
Name = (translations == null) ? "" : translations.Name,
Url = entity.Url,
Active = entity.Active
});
正如您所看到的那样，当我返回时，我从查询右侧只得到一行所有左行。
As you can see I'm getting just one row from the right side of the query while I return all the left rows.
我需要的是，而不是按照LanguageCode进行排序，我需要先通过一个特定的LanguageCode来排序查询，然后再进行排序。
What I need is, instead sorting just by LanguageCode, I need to tell the query to sort by a specifid LanguageCode first and then the rest.
如果我添加：
.OrderBy(p => (p.LanguageCode == currentLanguage) ? 0 : 1)
其中currentLanguage只是一个包含语言en的字符串， fr...
所以查询是：
where currentLanguage is just a string containing the language "en", "fr"... so the query is:
var queryString = (from entity in _dbContext.Links
from translations in _dbContext.LinksTrs.Where(p => p.LinkId == entity.Id).OrderBy(p => (p.LanguageCode == currentLanguage) ? 0 : 1).Take(1).DefaultIfEmpty()
select new LinksAdminListModel()
{
Id = entity.Id,
Name = (translations == null) ? "" : translations.Name,
Url = entity.Url,
Active = entity.Active
});
我收到以下错误：
未知列'Extent1.Id'在'where条款'
I get the following error: "Unknown column 'Extent1.Id' in 'where clause'"
我需要帮助，因为我需要返回当前语言（如果不存在），否则不需要。
I need help since I need to return the current language if it exists, if not the next one.
谢谢。
编辑
问题是ORDER与TAKE（1 ），但我确实需要采取第一个...
The problem is the combination of the ORDER with the TAKE(1), but I do need to take just the first one...
编辑2
这个问题似乎这是我以前评论过的...这是我使用的查询：
The probelm seems to be what I commented above...this is the query I'm using:
var queryString = (from entity in _dbContext.Links
from translations in _dbContext.LinksTrs.Where(p => p.LinkId == entity.Id).OrderByDescending(p => p.LanguageCode == currentLanguage).ThenBy(p => p.LanguageCode).Take(1)
select new LinksAdminListModel()
{
Id = entity.Id,
Name = (translations == null) ? "" : translations.Name,
Url = entity.Url,
Active = entity.Active
});
这是它正在生成的SQL ...如果有人可以看到这里的东西： / p>
And this is the SQL it is generating...in case someone can see something in here:
SELECT
`Apply1`.`Id`,
`Apply1`.`Name`,
`Apply1`.`Url`,
`Apply1`.`Active`
FROM (SELECT
`Extent1`.`Id`,
`Extent1`.`OrderPos`,
`Extent1`.`Url`,
`Extent1`.`Active`,
`Extent1`.`DateCreated`,
`Extent1`.`DateModified`,
`Extent1`.`UserIdModified`,
(SELECT
`Project1`.`C1`
FROM (SELECT
CASE WHEN ((`Extent2`.`LanguageCode` = 'es-ES') OR ((`Extent2`.`LanguageCode` IS NULL) AND ('es-ES' IS NULL))) THEN (1) WHEN (NOT ((`Extent2`.`LanguageCode` = 'es-ES') AND ((CASE WHEN (`Extent2`.`LanguageCode` IS NULL) THEN (1) ELSE (0) END) = (CASE WHEN ('es-ES' IS NULL) THEN (1) ELSE (0) END)))) THEN (0) END AS `C1`,
`Extent2`.`Name`,
`Extent2`.`LanguageCode`
FROM `LinkTr` AS `Extent2`
WHERE `Extent2`.`LinkId` = `Extent1`.`Id`) AS `Project1`
ORDER BY
`Project1`.`C1` DESC,
`Project1`.`LanguageCode` ASC LIMIT 1) AS `C1`,
(SELECT
`Project1`.`Name`
FROM (SELECT
CASE WHEN ((`Extent2`.`LanguageCode` = 'es-ES') OR ((`Extent2`.`LanguageCode` IS NULL) AND ('es-ES' IS NULL))) THEN (1) WHEN (NOT ((`Extent2`.`LanguageCode` = 'es-ES') AND ((CASE WHEN (`Extent2`.`LanguageCode` IS NULL) THEN (1) ELSE (0) END) = (CASE WHEN ('es-ES' IS NULL) THEN (1) ELSE (0) END)))) THEN (0) END AS `C1`,
`Extent2`.`Name`,
`Extent2`.`LanguageCode`
FROM `LinkTr` AS `Extent2`
WHERE `Extent2`.`LinkId` = `Extent1`.`Id`) AS `Project1`
ORDER BY
`Project1`.`C1` DESC,
`Project1`.`LanguageCode` ASC LIMIT 1) AS `Name`,
(SELECT
`Project1`.`LanguageCode`
FROM (SELECT
CASE WHEN ((`Extent2`.`LanguageCode` = 'es-ES') OR ((`Extent2`.`LanguageCode` IS NULL) AND ('es-ES' IS NULL))) THEN (1) WHEN (NOT ((`Extent2`.`LanguageCode` = 'es-ES') AND ((CASE WHEN (`Extent2`.`LanguageCode` IS NULL) THEN (1) ELSE (0) END) = (CASE WHEN ('es-ES' IS NULL) THEN (1) ELSE (0) END)))) THEN (0) END AS `C1`,
`Extent2`.`Name`,
`Extent2`.`LanguageCode`
FROM `LinkTr` AS `Extent2`
WHERE `Extent2`.`LinkId` = `Extent1`.`Id`) AS `Project1`
ORDER BY
`Project1`.`C1` DESC,
`Project1`.`LanguageCode` ASC LIMIT 1) AS `LanguageCode`
FROM `Link` AS `Extent1`) AS `Apply1`
编辑3
实体是：
public class Link : EntityBase
{
public int OrderPos { get; set; }
public string Url { get; set; }
public bool Active { get; set; }
public virtual IEnumerable<LinkTr> Translations { get; set; }
}
public class LinkTr : EntityBaseTr
{
public string Name { get; set; }
public string Summary { get; set; }
//[ForeignKey("Link")]
public int LinkId { get; set; }
public virtual Link Link { get; set; }
//[ForeignKey("Language")]
public string LanguageCode { get; set; }
public virtual Language Languages { get; set; }
}
public class EntityBase
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] //[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }
public DateTime? DateCreated { get; set; }
public DateTime? DateModified { get; set; }
public int UserIdModified { get; set; }
}
public class EntityBaseTr
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public DateTime? DateCreated { get; set; }
}
推荐答案
无法与信息重复提供。
Cannot duplicate with the information provided.
在测试过程中，我注意到的唯一的一点是表达式（translations == null）？ ：translations.Name 正在生成 null 检查翻译表 ID 列，不确定您是否在您的实体中。
The only thing I've noticed during the testing was that the expression (translations == null) ? "" : translations.Name was generating null check for Translations table Id column, not sure if you have such in your entity.
在任何情况下，值得尝试以下替代方案，不包括此类支票：
In any case, it's worth trying the following alternative which does not include such a check:
var queryString = (
from entity in _dbContext.Links
let translationName = _dbContext.LinksTrs
.Where(p => p.LinkId == entity.Id)
.OrderBy(p => p.LanguageCode == currentLanguage ? 0 : 1)
.Select(p => p.Name)
.FirstOrDefault();
select new LinksAdminListModel()
{
Id = entity.Id,
Name = translationName ?? "",
Url = entity.Url,
Active = entity.Active
});
更新：
事实证明，这是一个当前的MySql EF查询提供程序问题（bug？！）。
It turns out that this is a current MySql EF query provider issue (bug?!).
玩了一段时间后，我能够使其工作的唯一方法如下：
After playing a while, the only way I was able to make it work is as follows
var queryString =
(from entity in db.Links
let translations =
db.LinksTrs.Where(p => p.LinkId == entity.Id && p.LanguageCode == currentLanguage).FirstOrDefault() ??
db.LinksTrs.Where(p => p.LinkId == entity.Id).OrderBy(p => p.LanguageCode).FirstOrDefault()
select new LinksAdminListModel
{
Id = entity.Id,
Name = (translations == null) ? "" : translations.Name,
Url = entity.Url,
Active = entity.Active
});
这篇关于EF LInq左外联合排列拳头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
