既获得记录总数，又获得分页记录，这个怎么搞？

[APOLLO_TS]

var vipCards = (from p in eshopDataContext.VipCard
join c in eshopDataContext.Customer
on p.card_No equals c.customer_PostCode into CusCard
from x in CusCard.DefaultIfEmpty()
select p).Skip((pageSkip - 1) * maxPageSize).Take(maxPageSize);
int vipCardsCount = (from p in eshopDataContext.VipCard
join c in eshopDataContext.Customer
on p.card_No equals c.customer_PostCode into CusCard
from x in CusCard.DefaultIfEmpty()
select p).Count();

怎么把上边的合并成一个linq？

[APOLLO_TS]

[Quote=引用 1 楼 jiangshun 的回复:]

C# code
var Qurey= (from p in eshopDataContext.VipCard
join c in eshopDataContext.Customer
on p.card_No equals c.customer_PostCode into CusCard
select new{vipCards=CusCard,vipCardsCount=(from ……
[/Quote]

你用别的表 自己写一个看看。生成的SQL很怪异。

[-小蕾-]

帮顶。。。

[jiangshun]

var Qurey= (from p in eshopDataContext.VipCard
  join c in eshopDataContext.Customer
  on p.card_No equals c.customer_PostCode into CusCard
  select new{vipCards=CusCard,vipCardsCount=(from x in CusCard select x.card).Count()}).
    Skip((pageSkip - 1) * maxPageSize).Take(maxPageSize);

[Ny-6000]

那也好做,存储过程吧,一次多张表都可以返回.

[APOLLO_TS]

6楼辛苦了，可惜我要linq。
我目前已经有了一个写法，但是还不是很好。

[jiangshun]

[Quote=引用 3 楼 apollo_ts 的回复:]

引用 1 楼 jiangshun 的回复:

C# code
var Qurey= (from p in eshopDataContext.VipCard
join c in eshopDataContext.Customer
on p.card_No equals c.customer_PostCode into CusCard
select new{vipCards=CusCar……
[/Quote]

那结果呢？你要是关注生成的SQl，那就建议你写T-sql查询或者存储过程！

[lester19872007]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[sp_PageChange]
(
 @TableName varchar(50),            --表名
 @ReFieldsStr varchar(200) = '*',   --字段名(全部字段为*)
 @OrderString varchar(200),         --排序字段(必须!支持多字段不用加order by)
 @WhereString varchar(500) =N'',  --条件语句(不用加where)
 @PageSize int,                     --每页多少条记录
 @PageIndex int = 1 ,               --指定当前为第几页
 @TotalRecord int output            --返回总记录数
)
AS
 
BEGIN    

    --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    --第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END