这种两层嵌套的SQL语句如何改成LinQ？迷茫中

2012-02-03

这种两层嵌套的SQL语句怎么改成LinQ？迷茫中。SQL codeSELECTCV2.CV_CODE as CODE, CV1.CV_CODE as VALUE, C

这种两层嵌套的SQL语句怎么改成LinQ？迷茫中。

SQL code

                    SELECT    CV2.CV_CODE as CODE, CV1.CV_CODE as VALUE, CV2.CV_CONTENT+CV1.CV_CONTENT as CONTENT                    FROM [RiverEarth].[dbo].[CodeValue] as CV1,                            (                                SELECT [CV_CODE]                                      ,[CV_CONTENT]                                FROM [RiverEarth].[dbo].[CodeValue]                                where CV_CODE LIKE '0003%' --表示海堤                                AND LEN(CV_CODE)=12                            ) as CV2                    where CV1.CV_CODE LIKE CV2.CV_CODE+'%'                    AND LEN(CV1.CV_CODE)=16

以上是SQL查询语句

SQL code

if exists (select * from sysobjects where id = OBJECT_ID('[CodeValue]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [CodeValue]CREATE TABLE [CodeValue] ([CV_TYPE] [char]  (4) NOT NULL,[CV_CODE] [varchar]  (40) NOT NULL,[CV_CONTENT] [nvarchar]  (100) NULL,[CV_UNAME] [nvarchar]  (20) NULL,[CV_UDATE] [char]  (14) NULL)ALTER TABLE [CodeValue] WITH NOCHECK ADD  CONSTRAINT [PK_CodeValue] PRIMARY KEY  NONCLUSTERED ( [CV_CODE] )INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0001',N'00010001',N'發現災情，處理中',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0001',N'00010002',N'未發現災情，監控中',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0001',N'00010003',N'已完成巡視，無災情',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'00020001',N'檢查各項目及結果',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'000200010001',N'堤頂',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100010001',N'沉陷',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100010002',N'裂縫',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100010003',N'崩裂',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100010004',N'正常',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'000200010002',N'前坡',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100020001',N'沉陷',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100020002',N'裂縫',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100020003',N'崩裂',N'admin',N'201109221742')INSERT [CodeValue] ([CV_TYPE],[CV_CODE],[CV_CONTENT],[CV_UNAME],[CV_UDATE]) VALUES ( N'0002',N'0002000100020004',N'正常',N'admin',N'201109221742')

以上是数据库脚本
请问如何达到SQL语句的那种效果。
LinQ,自己把自己搞糊涂了。唉。

[解决办法]

C# code

//又见如此好的提问方式，赞一个var query=from cv1 in db.CodeValue          let temp=from c in db.CodeValue                   where c.CV_CODE.EndWith("0003")                         && c.CV_CODE.Length==12                   select new {c.CV_CODE,c.CV_CONTENT}          from cv2 in temp          where cv1.CV_CODE.EndWith(CV2.CV_CODE)               && cv1.CV_CODE.Length==16          select new           {               CODE=cv2.CV_CODE,               VALUE=cv1.CV_CODE,               CONTENT=cv2.CV_CONTENT+cv1.CV_CONTENT            };
[解决办法]
对数据源加上 .ToList() 方法就可以调用了.

热点排行

.NET

这种两层嵌套的SQL语句如何改成LinQ？迷茫中