c# - ASP.NET C# MVC 实体框架
问题描述
我正在学习 ASP.NET MVC,我一直坚持使用实体框架创建复杂的 SQL——它并不复杂,但它可能会变得复杂。
我的常规 ASP.NET C# webform 看起来像这样,我想将其转换为 ASP.NET MVC 和实体框架。
string date = Request.QueryString["date"] ?? "";
string strWhere = "";
string sqlTop = "20";
DateTime temp;
if (DateTime.TryParse(date, out temp))
{
strWhere = "WHERE CAST(NewsDate AS date)='" + temp.ToString("yyyy-MM-dd") + "'";
sqlTop = "50";
}
using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["NewsDB"].ConnectionString))
{
con.Open();
string selectSql = @"SELECT TOP " + sqlTop + @" *
, (SELECT TOP 1 FileName
FROM NewsImage
WHERE NewsGuid = N.NewsGuid
ORDER BY FileOrder ASC) AS FileName
FROM News N " + strWhere + @"
ORDER BY N.NewsDate DESC";
using (SqlCommand selectCmd = new SqlCommand(selectSql, con))
{
using (SqlDataReader selectDr = selectCmd.ExecuteReader())
{
while (selectDr.Read())
{
//My Output logic goes here
}
}
}
}
我的问题是如何使用实体框架实现这种类型的 SQL。
我已经创建了实体并DbContext
使用数据库优先的方法进行了设置。
private NewsEntities db = new NewsEntities();
我有两个数据库表: News & NewsImage 单个新闻可以有多个 newsimage (1-N)
我试过实现这样的目标:
新闻类
public class NewsClass
{
public News NewsDetails { get; set; }
public NewsImage NewsImages { get; set; }
}
型号:新闻
public partial class News
{
public int Id { get; set; }
public System.Guid NewsGuid { get; set; }
public string Heading { get; set; }
public string Text { get; set; }
public string Author { get; set; }
public System.DateTime NewsDate { get; set; }
}
型号:NewsImage
public partial class NewsImage
{
public int Id { get; set; }
public System.Guid NewsGuid { get; set; }
public string FileName { get; set; }
public int FileOrder { get; set; }
}
控制器:
var newsList = from n in news
join img in newsImages on n.NewsGuid equals img.NewsGuid into table1
from img in table1.DefaultIfEmpty()
select new NewsClass { NewsDetails = n, NewsImage = img };
从这里我被卡住了......子查询上的“选择前1个”,如何实现这一点
变得更好......现在我做了这样的事情:
var newsImages = db.NewsImages.OrderBy(n => n.NewsGuid).OrderBy(n => n.FileOrder).Where(n => n.FileOrder == 10);
var allNews = db.News.OrderByDescending(n => n.NewsDate).Take(sqlTop);
if (validDate)
allNews = allNews.Where(n => n.NewsDate.Year == newsDate.Year && n.NewsDate.Month == newsDate.Month && n.NewsDate.Day == newsDate.Day);
现在我需要将这两个对象“newsImages”和“allNews”合并到一个列表“newsList”中。
如何将两个列表查询 allNews 和 newsImages 合并到一个列表中,如下所示:
select new NewsList
{
NewsGuid = n.NewsGuid,
Heading = n.Heading,
FileName = ni.FileName
}).Take(sqlTop).ToList<NewsList>();
解决方案
实体框架(和其他 ORM)的主要好处是您不必再编写原始 SQL 来实现您的目标。OR 映射器创建了一个很好用的对象结构,您可以使用它来进行查询。
在你的情况下 - 你会做类似的事情:
string date = Request.QueryString["date"] ?? "";
int sqlTop = 20;
DateTime? newsDate = null;
if (DateTime.TryParse(date, out DateTime temp))
{
newsDate = temp;
sqlTop = 50;
}
using (NewsEntities db = new NewsEntities())
{
// grab the "NewsClass" entries - include the "NewsDetails" and
// "NewsImage" navigation property
var query = db.NewsClass
.Include(n => n.NewsDetails);
.Include(n => n.NewsImage);
// if you have a valid date - use that to refine the query
if (newsDate.HasValue)
{
query = query.Where(n => n.NewsDetails.NewsDate == newsDate.Value);
}
// order by NewsDate, and select only "sqlTop" entries
query = query.OrderByDescending(n => n.NewsDetails.NewsDate
.Take(sqlTop);
// now you can iterate over the "News" objects returned from the query
foreach(News n in query)
{
// get the first image from navigation property
var firstImage = n.NewsImage
.OrderBy(img => img.FileOrder)
.FirstOrDefault();
// do whatever you want with your news - output what you need
}
}