linq多单表和多表的linq多个条件查询询
点击联系发帖人
时间：2014-10-14 14:43
LINQ语句联合查询
LINQ语句联合查询，具体见例子如下：
List&TB_table1& GetListSHJL(string
ETPRS_CODE, string strZYKDJ, string strQYFL)
&&&&&&&&&&&
List&TB_table1& list = new
List&TB_table1&();
&&&&&&&&&&&
using (ModuleZYKEntities entitiy = new ModuleZYKEntities())
&&&&&&&&&&&
&&&&&&&&&&&&&&&
var query = from p in entitiy.TB_table_a
&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
(from q in entitiy.TB_table_b
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
where q.ETPRS_CODE == ETPRS_CODE
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&& q.QYLB == strZYKDJ
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&& q.DWLX_XH == strQYFL
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&
on p.CODE equals t.SP_CODE
&&&&&&&&&&&&&&&&&&&&&&&&&&&
//orderby p.CRT_DATE descending
&&&&&&&&&&&&&&&&&&&&&&&&&&&
select new TB_table1
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_DATE_XH = t.SP_DATE_XH,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_CODE = t.SP_CODE,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_DATE = t.SP_DATE,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_YJ = t.SP_YJ,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CRT_USER = t.CRT_USER,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_ZT = p.SP_ZT
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
query.OrderByDescending(s=&s.SP_DATE).ToList();
&&&&&&&&&&&
&&&&&&&&&&&
/// &summary&
/// &/summary&
public class TB_table1
&&&&&&&&&&&
public string SP_DATE_XH
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public string SP_CODE
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public DateTime SP_DATE
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public string SP_YJ
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
/// &summary&
&&&&&&&&&&&
/// 创建人
&&&&&&&&&&&
/// &/summary&
&&&&&&&&&&&
public string CRT_USER
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public string SP_ZT
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。asp.net实现的MVC跨数据库多表联合动态条件查询功能示例
作者：Mr-Robot
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了asp.net实现的MVC跨数据库多表联合动态条件查询功能,结合实例形式较为详细分析了asp.net基于MVC架构的跨数据库多表联合查询功能实现技巧,需要的朋友可以参考下
本文实例讲述了asp.net实现的MVC跨数据库多表联合动态条件查询功能。分享给大家供大家参考，具体如下：
一、控制器中方法
public ActionResult Search()
ViewBag.HeadTitle = "搜索";
ViewBag.MetaKey = "\"123\"";
ViewBag.MetaDes = "\"456\"";
string whereText = "";
if (Security.HtmlHelper.GetQueryString("first", true) != string.Empty)
whereText += " and a.ParentId='" + StringFilter("first", true)+"'";
if (Security.HtmlHelper.GetQueryString("second", true) != string.Empty)
whereText += " and a.categoryId='" + StringFilter("second",true)+"'";
string valueStr = "";
if (Security.HtmlHelper.GetQueryString("theme", true) != string.Empty)
valueStr += StringFilter("theme", true) + ",";
if (Security.HtmlHelper.GetQueryString("size", true) != string.Empty)
valueStr += StringFilter("size", true) + ",";
if (Security.HtmlHelper.GetQueryString("font", true) != string.Empty)
valueStr += StringFilter("font", true) + ",";
if (Security.HtmlHelper.GetQueryString("shape", true) != string.Empty)
valueStr += StringFilter("shape", true) + ",";
if (Security.HtmlHelper.GetQueryString("technique", true) != string.Empty)
valueStr += StringFilter("technique", true) + ",";
if (Security.HtmlHelper.GetQueryString("category", true) != string.Empty)
valueStr += StringFilter("category", true) + ",";
if (Security.HtmlHelper.GetQueryString("place", true) != string.Empty)
valueStr += StringFilter("place", true) + ",";
if (Security.HtmlHelper.GetQueryString("price", true) != string.Empty)
valueStr += StringFilter("price", true) + ",";
if (valueStr != "")
valueStr=valueStr.Substring(0, valueStr.Length - 1);
whereText += " and f.valueId in("+valueStr+")";
if (Security.HtmlHelper.GetQueryString("searchKeys", true) != string.Empty)
whereText += " and a.SaleTitle like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleDes like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleAuthor like '%'" + StringFilter("searchKes", true) + "'%' or a.KeyWords like '%'" + StringFilter("searchKes", true) + "'%' or g.valueProperty like '%'" + StringFilter("searchKes", true) + "'%'";
int pageSize = 50;
int pageIndex = HttpContext.Request.QueryString["pageIndex"].Toint(1);
List&string& searchInfo = Search(pageIndex, pageSize, whereText, 1);
if (Security.HtmlHelper.GetQueryString("sort", true) != string.Empty)
string sort = StringFilter("sort", true);
switch (sort)
//综合即默认按照上架时间降序排列即按照id降序
searchInfo = Search(pageIndex, pageSize, whereText, 1);
searchInfo = Search(pageIndex, pageSize, whereText,0, "saleTotal");
searchInfo = Search(pageIndex, pageSize, whereText,0, "favoritesTotal");
//价格升序
searchInfo = Search(pageIndex, pageSize, whereText,1);
//价格降序
searchInfo = Search(pageIndex, pageSize, whereText,2);
string jsonStr = searchInfo[0];
ViewData["jsondata"] = jsonS
int allCount = Utility.Toint(searchInfo[1], 0);
ViewBag.AllCount = allC
ViewBag.MaxPages = allCount % pageSize == 0 ? allCount / pageSize : (allCount / pageSize + 1).Toint(1);
return View();
[NonAction]
public List&string& Search(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId")
BLL.Products searchInfoBLL = new BLL.Products();
List&string& searchInfo = searchInfoBLL.GetSearchInfo(pageIndex, pageSize, whereText, orderByPrice,orderBy);
return searchI
注：Security.HtmlHelper.GetQueryString(），StringFilter(）为自己封装的方法，用于过滤参数值
二、BLL层方法
using System.W
using System.Web.C
using System.C
using System.Collections.G
using System.L
using System.T
using System.D
using System.Web.Script.S
using FotosayMall.M
using System.Text.RegularE
using System.IO;
using Newtonsoft.J
using Newtonsoft.Json.C
using FotosayMall.MVC.M
namespace FotosayMall.BLL
public class Products
private readonly DAL.Products dal = new DAL.Products();
/// &summary&
/// 分页查询，检索页数据
/// &/summary&
/// ¶m name="pageIndex"&&/param&
/// ¶m name="pageSize"&&/param&
/// ¶m name="orderByPrice"&价格排序：0默认，1升序，2降序&/param&
/// &returns&&/returns&
public List&string& GetSearchInfo(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId")
DataSet searchInfoTables = dal.GetSearchInfo(pageIndex, pageSize, whereText);
//总记录数
int allCount = Utility.Toint(searchInfoTables.Tables[1].Rows[0]["rowsTotal"], 0);
var searchInfo = from list in searchInfoTables.Tables[0].AsEnumerable().OrderByDescending(x =& x.Table.Columns[orderBy])
select new SearchModel
Url = "/home/products?saleId=" + list.Field&int&("SaleId"),
Author = list.Field&string&("SaleAuthor"),
PhotoFileName = list.Field&string&("PhotoFileName"),
PhotoFilePathFlag = list.Field&int&("PhotoFilePathFlag"),
Province = list.Field&string&("Place").Split(' ').First(),
SalePrice = list.Field&decimal&("SalePrice"),
UsingPrice = list.Field&decimal&("usingPrice"),
Title = list.Field&string&("SaleTitle").Length & 30 ? list.Field&string&("SaleTitle").Substring(0, 30) : list.Field&string&("SaleTitle"),
Year = list.Field&DateTime&("BuildTime").ToString("yyyy") == "1900" ? "" : list.Field&DateTime&("BuildTime").ToString("yyyy年")
if (orderByPrice==2)
searchInfo = searchInfo.OrderByDescending(x =& x.Price);
else if (orderByPrice == 1)
searchInfo = searchInfo.OrderBy(x =& x.Price);
string jsonStr = JsonConvert.SerializeObject(searchInfo);
List&string& dataList = new List&string&();
dataList.Add(jsonStr);
dataList.Add(allCount.ToString());
return dataL
注：注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。
/// &summary&
/// 获取检索页数据
/// &/summary&
/// ¶m name="pageIndex"&&/param&
/// ¶m name="pageSize"&&/param&
/// &returns&&/returns&
public DataSet GetSearchInfo(int pageIndex, int pageSize, string whereText)
StringBuilder sqlText = new StringBuilder();
sqlText.Append("select * from (");
sqlText.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum ");
sqlText.Append("from fotosay..Photo_Sale a join fotosay..Photo_Basic b on a.PhotoId = b.PhotoID ");
sqlText.Append("join fotosay..System_AccountsDescription c on b.UserID = c.UserID ");
sqlText.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId ");
sqlText.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId ");
sqlText.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId ");
sqlText.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId ");
sqlText.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId ");
sqlText.Append("where a.Status=1 " + whereText + " ");
sqlText.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal ");
sqlText.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageI");
sqlText.Append("select count(distinct a.saleId) rowsTotal from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";");
DbParameter[] parameters = {
Fotosay.CreateInDbParameter("@PageIndex", DbType.Int32,pageIndex),
Fotosay.CreateInDbParameter("@PageSize", DbType.Int32,pageSize)
DataSet searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlText.ToString(), parameters);
//记录条数不够一整页,则查历史库
if (searchInfoList.Tables[0].Rows.Count & pageSize)
string sql = "select top(1) a.saleId from fotosay..Photo_Sale a join fotosay..Photo_Basic_History b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";";
DataSet ds = Fotosay.ExecuteQuery(CommandType.Text, sql.ToString(), parameters);
if (ds != null && ds.Tables[0].Rows.Count & 0)
StringBuilder sqlTextMore = new StringBuilder();
sqlTextMore.Append("select * from (");
sqlTextMore.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum ");
sqlTextMore.Append("from fotosay..Photo_Sale a ");
sqlTextMore.Append("join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID ");
sqlTextMore.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId ");
sqlTextMore.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId ");
sqlTextMore.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId ");
sqlTextMore.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId ");
sqlTextMore.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId ");
sqlTextMore.Append("where a.Status=1 " + whereText + " ");
sqlTextMore.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal");
sqlTextMore.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageI");
sqlTextMore.Append("select count(distinct a.saleId) rowsTotal from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";");
searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlTextMore.ToString(), parameters);
return searchInfoL
注：注意其中使用的跨数据库查询的方式和union的一种使用方式
using System.Collections.G
using System.C
using System.L
using System.W
namespace FotosayMall.MVC.Models
public class SearchModel
/// &summary&
/// 原始图片文件夹(用于url地址)
/// &/summary&
private const string OriginImagesUrlFolder = "userimages/photos_origin";
/// &summary&
/// 购买页链接
/// &/summary&
public string Url { }
/// &summary&
/// 所属域名(1为fotosay，2为img，3为img1)
/// &/summary&
public int PhotoFilePathFlag { }
/// &summary&
/// 图片名称
/// &/summary&
public string PhotoFileName { }
/// &summary&
/// 商品名称
/// &/summary&
public string Title { }
/// &summary&
/// 作者所在省份
/// &/summary&
public string Province { }
/// &summary&
/// &/summary&
public string Author { }
/// &summary&
/// 创作年份
/// &/summary&
public string Year { }
/// &summary&
/// 图片：单次价格
/// &/summary&
public decimal UsingPrice { }
/// &summary&
/// 实物：定价
/// &/summary&
public decimal SalePrice { }
/// &summary&
/// &/summary&
public string Price
if (this.UsingPrice & 0)
return this.UsingPrice.ToString();
else if (this.SalePrice & 0)
return this.SalePrice.ToString();
return "议价";
/// &summary&
/// &/summary&
private string MasterSite
get { return ConfigurationManager.AppSettings["masterSite"].ToString(); }
/// &summary&
/// 图片完整路径
/// &/summary&
public string Img
return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg";
更多关于asp.net相关内容感兴趣的读者可查看本站专题：《》、《》、《》、《》、《》及《》。
希望本文所述对大家asp.net程序设计有所帮助。
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具2012年3月 总版技术专家分月排行榜第一
2013年7月 荣获微软MVP称号
2012年3月 总版技术专家分月排行榜第一
2013年7月 荣获微软MVP称号
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。}
LINQ语句联合查询，具体见例子如下：
List&TB_table1& GetListSHJL(string
ETPRS_CODE, string strZYKDJ, string strQYFL)
&&&&&&&&&&&
List&TB_table1& list = new
List&TB_table1&();
&&&&&&&&&&&
using (ModuleZYKEntities entitiy = new ModuleZYKEntities())
&&&&&&&&&&&
&&&&&&&&&&&&&&&
var query = from p in entitiy.TB_table_a
&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
(from q in entitiy.TB_table_b
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
where q.ETPRS_CODE == ETPRS_CODE
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&& q.QYLB == strZYKDJ
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&& q.DWLX_XH == strQYFL
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&
on p.CODE equals t.SP_CODE
&&&&&&&&&&&&&&&&&&&&&&&&&&&
//orderby p.CRT_DATE descending
&&&&&&&&&&&&&&&&&&&&&&&&&&&
select new TB_table1
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_DATE_XH = t.SP_DATE_XH,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_CODE = t.SP_CODE,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_DATE = t.SP_DATE,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_YJ = t.SP_YJ,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
CRT_USER = t.CRT_USER,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SP_ZT = p.SP_ZT
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
query.OrderByDescending(s=&s.SP_DATE).ToList();
&&&&&&&&&&&
&&&&&&&&&&&
/// &summary&
/// &/summary&
public class TB_table1
&&&&&&&&&&&
public string SP_DATE_XH
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public string SP_CODE
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public DateTime SP_DATE
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public string SP_YJ
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
/// &summary&
&&&&&&&&&&&
/// 创建人
&&&&&&&&&&&
/// &/summary&
&&&&&&&&&&&
public string CRT_USER
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
&&&&&&&&&&&
public string SP_ZT
&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&
&&&&&&&&&&&
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。asp.net实现的MVC跨数据库多表联合动态条件查询功能示例
作者：Mr-Robot
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了asp.net实现的MVC跨数据库多表联合动态条件查询功能,结合实例形式较为详细分析了asp.net基于MVC架构的跨数据库多表联合查询功能实现技巧,需要的朋友可以参考下
本文实例讲述了asp.net实现的MVC跨数据库多表联合动态条件查询功能。分享给大家供大家参考，具体如下：
一、控制器中方法
public ActionResult Search()
ViewBag.HeadTitle = "搜索";
ViewBag.MetaKey = "\"123\"";
ViewBag.MetaDes = "\"456\"";
string whereText = "";
if (Security.HtmlHelper.GetQueryString("first", true) != string.Empty)
whereText += " and a.ParentId='" + StringFilter("first", true)+"'";
if (Security.HtmlHelper.GetQueryString("second", true) != string.Empty)
whereText += " and a.categoryId='" + StringFilter("second",true)+"'";
string valueStr = "";
if (Security.HtmlHelper.GetQueryString("theme", true) != string.Empty)
valueStr += StringFilter("theme", true) + ",";
if (Security.HtmlHelper.GetQueryString("size", true) != string.Empty)
valueStr += StringFilter("size", true) + ",";
if (Security.HtmlHelper.GetQueryString("font", true) != string.Empty)
valueStr += StringFilter("font", true) + ",";
if (Security.HtmlHelper.GetQueryString("shape", true) != string.Empty)
valueStr += StringFilter("shape", true) + ",";
if (Security.HtmlHelper.GetQueryString("technique", true) != string.Empty)
valueStr += StringFilter("technique", true) + ",";
if (Security.HtmlHelper.GetQueryString("category", true) != string.Empty)
valueStr += StringFilter("category", true) + ",";
if (Security.HtmlHelper.GetQueryString("place", true) != string.Empty)
valueStr += StringFilter("place", true) + ",";
if (Security.HtmlHelper.GetQueryString("price", true) != string.Empty)
valueStr += StringFilter("price", true) + ",";
if (valueStr != "")
valueStr=valueStr.Substring(0, valueStr.Length - 1);
whereText += " and f.valueId in("+valueStr+")";
if (Security.HtmlHelper.GetQueryString("searchKeys", true) != string.Empty)
whereText += " and a.SaleTitle like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleDes like '%'" + StringFilter("searchKes", true) + "'%' or a.SaleAuthor like '%'" + StringFilter("searchKes", true) + "'%' or a.KeyWords like '%'" + StringFilter("searchKes", true) + "'%' or g.valueProperty like '%'" + StringFilter("searchKes", true) + "'%'";
int pageSize = 50;
int pageIndex = HttpContext.Request.QueryString["pageIndex"].Toint(1);
List&string& searchInfo = Search(pageIndex, pageSize, whereText, 1);
if (Security.HtmlHelper.GetQueryString("sort", true) != string.Empty)
string sort = StringFilter("sort", true);
switch (sort)
//综合即默认按照上架时间降序排列即按照id降序
searchInfo = Search(pageIndex, pageSize, whereText, 1);
searchInfo = Search(pageIndex, pageSize, whereText,0, "saleTotal");
searchInfo = Search(pageIndex, pageSize, whereText,0, "favoritesTotal");
//价格升序
searchInfo = Search(pageIndex, pageSize, whereText,1);
//价格降序
searchInfo = Search(pageIndex, pageSize, whereText,2);
string jsonStr = searchInfo[0];
ViewData["jsondata"] = jsonS
int allCount = Utility.Toint(searchInfo[1], 0);
ViewBag.AllCount = allC
ViewBag.MaxPages = allCount % pageSize == 0 ? allCount / pageSize : (allCount / pageSize + 1).Toint(1);
return View();
[NonAction]
public List&string& Search(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId")
BLL.Products searchInfoBLL = new BLL.Products();
List&string& searchInfo = searchInfoBLL.GetSearchInfo(pageIndex, pageSize, whereText, orderByPrice,orderBy);
return searchI
注：Security.HtmlHelper.GetQueryString(），StringFilter(）为自己封装的方法，用于过滤参数值
二、BLL层方法
using System.W
using System.Web.C
using System.C
using System.Collections.G
using System.L
using System.T
using System.D
using System.Web.Script.S
using FotosayMall.M
using System.Text.RegularE
using System.IO;
using Newtonsoft.J
using Newtonsoft.Json.C
using FotosayMall.MVC.M
namespace FotosayMall.BLL
public class Products
private readonly DAL.Products dal = new DAL.Products();
/// &summary&
/// 分页查询，检索页数据
/// &/summary&
/// ¶m name="pageIndex"&&/param&
/// ¶m name="pageSize"&&/param&
/// ¶m name="orderByPrice"&价格排序：0默认，1升序，2降序&/param&
/// &returns&&/returns&
public List&string& GetSearchInfo(int pageIndex, int pageSize, string whereText, int orderByPrice, string orderBy = "SaleId")
DataSet searchInfoTables = dal.GetSearchInfo(pageIndex, pageSize, whereText);
//总记录数
int allCount = Utility.Toint(searchInfoTables.Tables[1].Rows[0]["rowsTotal"], 0);
var searchInfo = from list in searchInfoTables.Tables[0].AsEnumerable().OrderByDescending(x =& x.Table.Columns[orderBy])
select new SearchModel
Url = "/home/products?saleId=" + list.Field&int&("SaleId"),
Author = list.Field&string&("SaleAuthor"),
PhotoFileName = list.Field&string&("PhotoFileName"),
PhotoFilePathFlag = list.Field&int&("PhotoFilePathFlag"),
Province = list.Field&string&("Place").Split(' ').First(),
SalePrice = list.Field&decimal&("SalePrice"),
UsingPrice = list.Field&decimal&("usingPrice"),
Title = list.Field&string&("SaleTitle").Length & 30 ? list.Field&string&("SaleTitle").Substring(0, 30) : list.Field&string&("SaleTitle"),
Year = list.Field&DateTime&("BuildTime").ToString("yyyy") == "1900" ? "" : list.Field&DateTime&("BuildTime").ToString("yyyy年")
if (orderByPrice==2)
searchInfo = searchInfo.OrderByDescending(x =& x.Price);
else if (orderByPrice == 1)
searchInfo = searchInfo.OrderBy(x =& x.Price);
string jsonStr = JsonConvert.SerializeObject(searchInfo);
List&string& dataList = new List&string&();
dataList.Add(jsonStr);
dataList.Add(allCount.ToString());
return dataL
注：注意观察由DataTable转换为可枚举的可用于Linq查询的方法方式。
/// &summary&
/// 获取检索页数据
/// &/summary&
/// ¶m name="pageIndex"&&/param&
/// ¶m name="pageSize"&&/param&
/// &returns&&/returns&
public DataSet GetSearchInfo(int pageIndex, int pageSize, string whereText)
StringBuilder sqlText = new StringBuilder();
sqlText.Append("select * from (");
sqlText.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum ");
sqlText.Append("from fotosay..Photo_Sale a join fotosay..Photo_Basic b on a.PhotoId = b.PhotoID ");
sqlText.Append("join fotosay..System_AccountsDescription c on b.UserID = c.UserID ");
sqlText.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId ");
sqlText.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId ");
sqlText.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId ");
sqlText.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId ");
sqlText.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId ");
sqlText.Append("where a.Status=1 " + whereText + " ");
sqlText.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal ");
sqlText.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageI");
sqlText.Append("select count(distinct a.saleId) rowsTotal from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";");
DbParameter[] parameters = {
Fotosay.CreateInDbParameter("@PageIndex", DbType.Int32,pageIndex),
Fotosay.CreateInDbParameter("@PageSize", DbType.Int32,pageSize)
DataSet searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlText.ToString(), parameters);
//记录条数不够一整页,则查历史库
if (searchInfoList.Tables[0].Rows.Count & pageSize)
string sql = "select top(1) a.saleId from fotosay..Photo_Sale a join fotosay..Photo_Basic_History b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";";
DataSet ds = Fotosay.ExecuteQuery(CommandType.Text, sql.ToString(), parameters);
if (ds != null && ds.Tables[0].Rows.Count & 0)
StringBuilder sqlTextMore = new StringBuilder();
sqlTextMore.Append("select * from (");
sqlTextMore.Append("select a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,coalesce(e.BuildTime,0) BuildTime,c.Place,coalesce(d.usingPrice,0) usingPrice,coalesce(e.SalePrice,0) SalePrice,h.saleTotal,h.favoritesTotal,row_number() over(order by a.saleId) rowsNum ");
sqlTextMore.Append("from fotosay..Photo_Sale a ");
sqlTextMore.Append("join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID ");
sqlTextMore.Append("left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId ");
sqlTextMore.Append("left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId ");
sqlTextMore.Append("join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId ");
sqlTextMore.Append("join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId ");
sqlTextMore.Append("join fotosay..Photo_Sale_Property h on a.saleId = h.saleId ");
sqlTextMore.Append("where a.Status=1 " + whereText + " ");
sqlTextMore.Append("group by a.SaleId,a.PhotoId,SaleTitle,SaleAuthor,a.Status,a.categoryId,c.UserID,c.UserName,b.PhotoFilePathFlag,b.PhotoFileName,e.BuildTime,c.Place,usingPrice,SalePrice,h.saleTotal,h.favoritesTotal");
sqlTextMore.Append(") t where rowsNum between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageI");
sqlTextMore.Append("select count(distinct a.saleId) rowsTotal from fotosay..Photo_Sale a join (select b1.PhotoFilePathFlag,b1.PhotoFileName,b1.UserID,b1.PhotoID from fotosay..Photo_Basic b1 union select b2.PhotoFilePathFlag,b2.PhotoFileName,b2.UserID,b2.PhotoID from fotosay..Photo_Basic_History b2 ) b on a.PhotoId = b.PhotoID join fotosay..System_AccountsDescription c on b.UserID = c.UserID left join fotosay..Photo_Sale_Picture d on a.SaleId = d.SaleId left join fotosay..Photo_Sale_Tangible e on a.saleId = e.saleId join FotosayMall..Fotomall_Product_Relation f on f.saleId = a.SaleId join FotosayMall..Fotomall_Product_PropertyValue g on g.categoryId = a.categoryId and g.valueId = f.valueId and g.propertyId = f.propertyId join fotosay..Photo_Sale_Property h on a.saleId = h.saleId where a.Status=1 " + whereText + ";");
searchInfoList = Fotosay.ExecuteQuery(CommandType.Text, sqlTextMore.ToString(), parameters);
return searchInfoL
注：注意其中使用的跨数据库查询的方式和union的一种使用方式
using System.Collections.G
using System.C
using System.L
using System.W
namespace FotosayMall.MVC.Models
public class SearchModel
/// &summary&
/// 原始图片文件夹(用于url地址)
/// &/summary&
private const string OriginImagesUrlFolder = "userimages/photos_origin";
/// &summary&
/// 购买页链接
/// &/summary&
public string Url { }
/// &summary&
/// 所属域名(1为fotosay，2为img，3为img1)
/// &/summary&
public int PhotoFilePathFlag { }
/// &summary&
/// 图片名称
/// &/summary&
public string PhotoFileName { }
/// &summary&
/// 商品名称
/// &/summary&
public string Title { }
/// &summary&
/// 作者所在省份
/// &/summary&
public string Province { }
/// &summary&
/// &/summary&
public string Author { }
/// &summary&
/// 创作年份
/// &/summary&
public string Year { }
/// &summary&
/// 图片：单次价格
/// &/summary&
public decimal UsingPrice { }
/// &summary&
/// 实物：定价
/// &/summary&
public decimal SalePrice { }
/// &summary&
/// &/summary&
public string Price
if (this.UsingPrice & 0)
return this.UsingPrice.ToString();
else if (this.SalePrice & 0)
return this.SalePrice.ToString();
return "议价";
/// &summary&
/// &/summary&
private string MasterSite
get { return ConfigurationManager.AppSettings["masterSite"].ToString(); }
/// &summary&
/// 图片完整路径
/// &/summary&
public string Img
return MasterSite + "/" + OriginImagesUrlFolder + this.PhotoFileName + "b.jpg";
更多关于asp.net相关内容感兴趣的读者可查看本站专题：《》、《》、《》、《》、《》及《》。
希望本文所述对大家asp.net程序设计有所帮助。
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具2012年3月 总版技术专家分月排行榜第一
2013年7月 荣获微软MVP称号
2012年3月 总版技术专家分月排行榜第一
2013年7月 荣获微软MVP称号
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。}
我要回帖
更多关于 linq 多条件查询 的文章
更多推荐
- ·欲贷款买车，哥瑞，思域，买卡罗拉还是本田XRV好-V选哪个？
- ·微信多出来一怎么查询自己有几张工商银行卡卡
- ·请问网络营销与直播电商专业好吗的学院能上吗？在四川的国际标榜职业学院网络营销与直播电商专业好吗可以吗？
- ·市场上为什么没有看到有销售可以充气护腰带的皮带？
- ·oppoa3价格多少pro手机怎么样？
- ·创维智能电视需要配个天赐棒吗
- ·微店的投诉如何取消？
- ·免租期内解除租赁合同同中租期从10月开始,签合同是9月份,在10月份前毁约可以全额
- ·76年9男属猴女属龙生什么属相孩子男和76年10生的女性配吗
- ·酷狗直播里面的大哥音乐Soho - Dj - 2013 暖场 大哥猛里的原版歌曲叫什么
- ·笔记本电脑时间开久了.电脑cpu风扇声音很大就很大.而且就很卡.前二天才刚刚清理干净.是不是那里出问题了.
- ·400度超薄镜片一般多少钱与普通镜片区别
- ·中国电信是全国接听免费吗?我的号是177
- ·我没百度阅读会员，谁帮我下载下这个文档，，，
- ·红袖添香签约作者收入仙侠文写的好的都有谁
- ·微博怎么关注明星星以后怎么知道他在微博上和粉丝互动,回答问题
- ·三星智能cntv中国网络电视台1看cntv高清节目会卡，我家8m光纤啊这都看不了？三星售后出来说说？
- ·如何用od或类似od的软o文件反编译译（破解或汉化）易语言程序
- ·今天1078私家车武汉1078广播主持人台18点一刻播的一首歌叫什么名字
- ·想买个手机1000左右机
- ·linq多单表和多表的linq多个条件查询询
- ·大众途锐参数发动机发动机电脑匹配燃油路加注
- ·C(14) : error C2059: syntax error before: 'type'这是哪错了啊，求大神！！！
- ·6个0·3在o里填上适当的运算符号号或括号使其等于4
- ·viv0x5l上网页面字体怎样ps放大缩小快捷键
- ·载了个fl studio安卓手机版 11.5 但是打不开，this version has expired怎么破
- ·怎么让客人知道我们店有美团退款商家知道吗网?
- ·华安证券通达信版lpo是否已中止
- ·小花仙第三季微博壕友乎微博(⊙o⊙)…
- ·《短歌行 感情赏析》与《龟虽寿》艺术手法和感情上的异同 ps：请看清题目再回答，谢谢
- ·gts450 512MB ddr5 ddr5显卡怎么样插头是PCI—E的吗？需要多少的电源啊，需要6pin吗
- ·三星root三星用什么软件root好
- ·请问下，这个是什么问题？原来都没有的，前两天有点红不会疼不会痒，今天小美女洗完澡出来发现起泡了，现在有点疼
- ·中国医科大学博士出路有没有建立博士论文数据库
