与2个相关实体的LINQ请求 [英] LINQ request with 2 related entities

问题描述

我有以下代码:

        var result = from i in _dbContext.Users
                     orderby i.aspnet_User.aspnet_Membership.CreateDate
                     select new ITW2012Mobile.Models.BusinessLogic.PreReg()
                     {
                         UserID = i.ID,
                         Company = i.Company,
                         FirstName = i.FirstName,
                         LastName = i.LastName,
                         IsUsed = i.IsUsed,
                         Title = i.Title,
                         Email = ((i.IsUsed) ? i.aspnet_User.aspnet_Membership.Email : i.PreRegEmail.Email)
                     };
        var q = result.ToList();

因此，当flag isUsed == true时，我们需要从成员资格中获取电子邮件，否则从PreRegEmail中获取电子邮件.但是此代码不起作用，得到无法解决SELECT语句中第7列的排序规则冲突.".为什么以及如何解决?

so, when flag isUsed == true we need get email from membership, otherwise from PreRegEmail. But this code does not work, get "Cannot resolve collation conflict for column 7 in SELECT statement.". Why and how to solve?

推荐答案

The error message is actually coming from sql server. You can not combine two columns with different collation into one. I would recommend using linq-to-entities to select them into separate properties and than linq-to-objects to construct your Email property like so

var result = from i in _dbContext.Users
             orderby i.aspnet_User.aspnet_Membership.CreateDate
             select new 
             {
               UserID = i.ID,
               Company = i.Company,
               FirstName = i.FirstName,
               LastName = i.LastName,
               IsUsed = i.IsUsed,
               Title = i.Title,
               AspEmail = i.aspnet_User.aspnet_Membership.Email,
               PreRegEmail = i.PreRegEmail.Email,
             };   

var q = from i in result.AsEnumerable()
        select new ITW2012Mobile.Models.BusinessLogic.PreReg()
        {
          UserID = i.ID,
          Company = i.Company,
          FirstName = i.FirstName,
          LastName = i.LastName,
          IsUsed = i.IsUsed,
          Title = i.Title,
          Email = i.IsUsed ? i.AspEmail  : i.PreRegEmail,
        };

var final = q.ToList();

这篇关于与2个相关实体的LINQ请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！