我有这个左外连接查询
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左外联合排列拳头
发布评论