左外部使用MVC模型将Linq连接到实体

本文关键字：Linq 连接 实体 模型 外部 MVC | 更新日期: 2023-09-27 18:24:37

我已经研究过了，如果这是重复的，我很抱歉，但我似乎找不到正确的答案，我从这个网站学到了很多。

我有3张桌子：

CREATE TABLE [Reporting].[ReportingCompanies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EINC] [int] NOT NULL,
[CompanyId] [varchar](4) NOT NULL,
[CompanyName] [varchar](50) NOT NULL,
[LastClosedWeek] [datetime] NOT NULL,
[PriorWeekSales] [decimal](14, 4) NULL,
[PriorWeek] [datetime] NULL,
[CurrentWeek] [datetime] NULL,
[LastWeekSales] [decimal](14, 4) NULL,
[AgingBalance] [decimal](14, 4) NULL,
[DataAsOf] [datetime] NULL,
[Tier] [int] NULL,
[CurrentWeeklyAverage] [decimal](14, 4) NULL,
[AvgAged] [decimal](14, 4) NULL,
[hasSpread] [bit] NULL,
[DSO] [int] NULL,
[isFamily] [bit] NULL,
[ImportStatus] [bit] NULL,
[ClientStatus] [varchar](50) NULL,
 CONSTRAINT [PK_ReportingCompanies] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
CREATE TABLE [Reporting].[AllowedCompanies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustomUserProfileUserId] [uniqueidentifier] NOT NULL,
[CustomUserProfileID] [int] NOT NULL,
[ReportingCompanyID] [int] NOT NULL,
 CONSTRAINT [PK_AllowedCompanies] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Reporting].[CustomUserProfiles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
[LoweredUserName] [nvarchar](256) NOT NULL,
[MobileAlias] [nvarchar](16) NULL,
[IsAnonymous] [bit] NOT NULL,
[LastActivityDate] [datetime] NOT NULL,
[ImportStatus] [bit] NULL,
 CONSTRAINT [PK_CustomUserProfiles] PRIMARY KEY CLUSTERED 
(
[UserId] ASC,
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]

现在，我需要使用左外部联接添加第四个表，以便在不存在记录的情况下仅获得VisitCount列的默认值0。

CREATE TABLE [PayJot].[FavoriteClients](
[FavRowID] [int] IDENTITY(1,1) NOT NULL,
[Userid] [uniqueidentifier] NOT NULL,
[CompanyId] [int] NOT NULL,
[VisitCount] [int] NOT NULL,
 CONSTRAINT [PK_FavoriteClients] PRIMARY KEY CLUSTERED 
(
    [FavRowID] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这是我的最新尝试，它返回错误"实体或复杂类型'DALModel.FFavoriteClient'不能在LINQ to Entities查询中构造"，但这正是intellisense似乎要求的（？）

            var userName = HttpContext.Current.User.Identity.Name;
        var q = from c in db.ReportingCompanies1
                join a in db.AllowedCompanies on new { ID = (int)c.ID } equals new { ID = a.ReportingCompanyID }
                join p in db.CustomUserProfiles on new { CustomUserProfileID = a.CustomUserProfileID } equals new { CustomUserProfileID = p.ID }
                join f in db.FavoriteClients on new { A = c.ID, B = p.UserId } equals new { A = f.CompanyId, B = f.Userid } into fvc
                from userfavs in fvc.DefaultIfEmpty(new FavoriteClient { CompanyId = c.ID, Userid = p.UserId , VisitCount = 0  })
                where
                  p.UserName == userName
                orderby c.CompanyName
                select new Clients()
                {
                    ID = c.ID,
                    CompanyId = c.CompanyId,
                    CompanyName = c.CompanyName,
                    visitCount = userfavs.VisitCount
                };
            ObservableCollection<Clients> clientList = new ObservableCollection<Clients>(q.ToList());

我是Linq的新手，虽然我读到的所有例子都有道理，但它们在实现中失败得很惨。如果可以的话，请提前告知。

左外部使用MVC模型将Linq连接到实体

我刚刚开始工作：

        var q = from p in db.CustomUserProfiles
                join a in db.AllowedCompanies on new { ID = p.ID } equals new { ID = a.CustomUserProfileID }
                join c in db.ReportingCompanies1 on new { ReportingCompanyID = (int)(int)a.ReportingCompanyID } equals new { ReportingCompanyID = c.ID }
                join f in db.FavoriteClients
                      on new { a.CustomUserProfileUserId, ReportingCompanyID = a.ReportingCompanyID }
              equals new { CustomUserProfileUserId = (Guid)f.Userid, ReportingCompanyID = f.CompanyId } into FavoriteClients_join
                from FavoriteClients in FavoriteClients_join.DefaultIfEmpty()
                where
                    p.UserName == userName
                orderby
                    FavoriteClients.VisitCount descending, 
                    c.CompanyName
                select new Clients()
                {
                    ID = c.ID,
                    CompanyId = c.CompanyId,
                    CompanyName = c.CompanyName,
                    visitCount = ((Int32?)FavoriteClients.VisitCount ?? (Int32)0)
                };
相关文章：