如何从几个表中选择值 [英] How to select Values from several tables
问题描述
我的三个表结构是:
USE [DB_OrderV2]
GO
/****** Object: Table [dbo].[tblPageInfo] Script Date: 07/24/2010 23:16:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblPageInfo](
[Code] [int] IDENTITY(1,1) NOT NULL,
[PageID] [smallint] NOT NULL,
[PageName] [nvarchar](80) NOT NULL,
[Description] [nvarchar](80) NOT NULL,
[Status] [tinyint] NOT NULL,
[CreateUserID] [smallint] NULL,
[CreateDate] [smalldatetime] NULL,
[UpdateUserID] [smallint] NULL,
[UpdateDate] [smalldatetime] NULL,
CONSTRAINT [PK_tblPageInfo] PRIMARY KEY CLUSTERED
(
[PageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [DB_OrderV2]
GO
/****** Object: Table [dbo].[tblUserType] Script Date: 07/24/2010 23:17:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblUserType](
[Code] [int] IDENTITY(1,1) NOT NULL,
[UserTypeID] [smallint] NOT NULL,
[UserType] [nvarchar](50) NOT NULL,
[Status] [tinyint] NULL,
[CreateUserID] [smallint] NULL,
[CreateDate] [smalldatetime] NULL,
[UpdateUserID] [smallint] NULL,
[UpdateDate] [smalldatetime] NULL,
CONSTRAINT [PK_tblUserType] PRIMARY KEY CLUSTERED
(
[UserTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [DB_OrderV2]
GO
/****** Object: Table [dbo].[tblUserInfo] Script Date: 07/24/2010 23:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblUserInfo](
[Code] [int] IDENTITY(1,1) NOT NULL,
[UserID] [smallint] NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[LoginName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[UserTypeID] [smallint] NOT NULL,
[Status] [tinyint] NOT NULL,
[ActiveDate] [datetime] NULL,
[CreateUserID] [smallint] NULL,
[CreateDate] [smalldatetime] NULL,
[UpdateUserID] [smallint] NULL,
[UpdateDate] [smalldatetime] NULL,
CONSTRAINT [PK_tblUserInfo_1] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
使用上表,我需要一个选择查询来选择几个字段.但是如何?
query = this.Context.tblUserInfos
.Where(p => p.Status == 1)
.Select(p => new { p.tblUserType.UserTypeID,p.tblUserType.UserType,});
return query;
上面的LINQ查询效果很好,但是在这里我需要从 tblUserRole 表中选择 UserRoleID ,并从 tblPageInfo 中选择 PageName . strong>表.在上面的查询中写什么以选择那些字段.
above LINQ query works well ,but here i need to select UserRoleID from tblUserRole table , and PageName from tblPageInfo table .What to write on above query to select those fields.
推荐答案
就像在SQL中一样,您将要使用联接.所以像
Just like in SQL you will want to use a join. So something like
query = from p in Context.tblUserInfos
join u in Context.tblUserRole Where p.UserRoleID equals u.UserRoleID
join n in context.tblPageInfo where p.PageName equals n.PageName
where p.Status == 1
select new {p.tblUserType.UserTypeId, p.tblUserType.UserType, u.UserRoleId, n.PageName};
我无法从每个表中解析出您想要加入的内容,但这应该可以帮助您入门.
I wasn't quite able to parse what you were trying to join on from each table, but that should get you started.
修改
我想我可能已经把语法弄糊涂了.
I think I may have gotten my syntax confused.
升级Labmda版本
query = Context.tblUserInfos
.Join(Context.tblUserRole, info => info.UserRoleID, user => user.UserRoleID, (info, user) => new {Info = info, User = user})
.Join(Context.tblPageInfo, info2 => info2.PageName, name => name.PageName, (info2, name) => new {Info2 = info2, Name = name})
.Where(p => p.Status == 1)
.Select(p => new { p.tblUserType.UserTypeID,p.tblUserType.UserType, User, Name});
我还没有测试过,但是我认为这是正确的.
I haven't tested that, but I think that's correct.
这篇关于如何从几个表中选择值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!