基于可选搜索参数选择数据使用实体框架 [英] Selecting Data Using Entity Framework based on optional search parameters
问题描述
我的Web服务具有与EF课程。 Web服务使用数据库，填充这些客户端类将其传递回客户端。例如....
在网络服务中
[DataContract]
public class MyMovie
{
[DataMember]
public int MovieId;
[DataMember]
public string MovieName;
[DataMember]
public int MovieRatingId;
[DataMember]
public string MovieRunTime;
[DataMember]
public string MovieIMDBUrl;
[DataMember]
public DateTime MovieDateAdded;
[DataMember]
public List< MyMovieActor>演员;
[DataMember]
public List< MyMovieMedia> MediaFiles;
}
public class MyMovieActor
{
[DataMember]
public int ActorId;
[DataMember]
public int ActorMovieId;
[DataMember]
public string ActorName;
[DataMember]
public string ActorCharacter;
}
public class MyMovieMedia
{
[DataMember]
public int MediaId;
[DataMember]
public int MediaMovieId;
[DataMember]
public string MediaFileLocation;
[DataMember]
public bool MediaDefault;
}
数据模型（简化）...
我的网络服务方法....
public MyMovie GetMovie（int movieId）
{
MyMovie response = new MyMovie（）;
使用（MovieDatabaseEntities DbContext = new MovieDatabaseEntities（））
{
var query =（从dbContext.Movies中的oData
其中oData.MovieId == movieId
select oData）.ToList（）;
if（query.Count> 0）
{
response.MovieDateAdded = query [0] .MovieDateAdded;
response.MovieId = query [0] .MovieId;
response.MovieIMDBUrl = query [0] .MovieIMDBUrl;
response.MovieName = query [0] .MovieName;
response.MovieRatingId = query [0] .MovieRatingId;
response.MovieRunTime = query [0] .MovieRunTime;
}
列表< MyMovieActor> actors = new List< MyMovieActor>（）;
foreach（MovieActor ma in query [0] .MovieActors）
{
MyMovieActor a = new MyMovieActor（）;
a.ActorId = ma.ActorId;
a.ActorMovieId = ma.ActorMovieId;
a.ActorCharacter = ma.ActorCharacter;
a.ActorName = ma.ActorName;
actors.Add（a）;
}
response.Actors = actors;
列表< MyMovieMedia> medias = new List< MyMovieMedia>（）;
foreach（MovieMedia mm in query [0] .MovieMedias）
{
MyMovieMedia med = new MyMovieMedia（）;
med.MediaId = mm.MediaId;
med.MediaMovieId = mm.MediaMovieId;
med.MediaFileLocation = mm.MediaFileLocation; // HDD Folder
med.MediaDefault = mm.MediaDefault; //图片显示在列表上
medias.Add（med）;
}
response.MediaFiles = medias;
}
返回响应;
}
所以现在我想创建
public List< MyMovie> GetMovies（string moviename，int movierating，string movieruntime）
其中所有参数将被填充或零长度字符串。如果他们有一个值，我想添加它作为where子句的一部分。
所以基本上更新
var query =（从DbContext.Movies中的oData
其中oData.MovieId == movieId
选择oData）.ToList（）;
相当于
var query =（从DbContext.Movies中的oData
其中1 = 1
if（String.IsSafe（moviename））
{
&& ; oData.MovieName Like％movieId％
}
if（movierating！= 0）
{
&& oData.MovieRating = movierating
}
if（String.IsSafe（movieruntime））
{
&& oData.MovieRuntime == movieruntime
}
select oData）.ToList（）;
看着这只是困惑了我更多，我总是用好的旧ADO写我的数据层。 NET，DataReaders，适配器，存储Procs等，但是这个项目我被要求使用EF。这让我很困惑他们对老狗和新技巧说什么？
对此的任何帮助将非常感谢。
IQueryable是你的朋友。它允许在多个步骤中组合查询，但它不会生成SQL并在需要之前执行（如由于迭代或ToList（）调用））。所以你的代码将如下所示：
var query =（从DbContext.Movies中的oData选择oData）;
if（！string.IsNullOrEmpty（moviename））
query = query.Where（m => m.MovieName.Contains（moviename））;
...
I have a project that using EF to interface with the database. It does this via a WCF web service. This is my first EF project and I am trying to work out how to construct a query based on optional parameters.
My web service has a data contract with matching fields to the classes in EF. The web service wueries the database, populates these client classes an passes them back to the client. For an example....
In the web service
[DataContract]
public class MyMovie
{
[DataMember]
public int MovieId;
[DataMember]
public string MovieName;
[DataMember]
public int MovieRatingId;
[DataMember]
public string MovieRunTime;
[DataMember]
public string MovieIMDBUrl;
[DataMember]
public DateTime MovieDateAdded;
[DataMember]
public List<MyMovieActor> Actors;
[DataMember]
public List<MyMovieMedia> MediaFiles;
}
public class MyMovieActor
{
[DataMember]
public int ActorId;
[DataMember]
public int ActorMovieId;
[DataMember]
public string ActorName;
[DataMember]
public string ActorCharacter;
}
public class MyMovieMedia
{
[DataMember]
public int MediaId;
[DataMember]
public int MediaMovieId;
[DataMember]
public string MediaFileLocation;
[DataMember]
public bool MediaDefault;
}
The data model (simplified)...
My web service method....
public MyMovie GetMovie(int movieId)
{
MyMovie response = new MyMovie();
using (MovieDatabaseEntities DbContext = new MovieDatabaseEntities())
{
var query = (from oData in DbContext.Movies
where oData.MovieId == movieId
select oData).ToList();
if (query.Count > 0)
{
response.MovieDateAdded = query[0].MovieDateAdded;
response.MovieId = query[0].MovieId;
response.MovieIMDBUrl = query[0].MovieIMDBUrl;
response.MovieName = query[0].MovieName;
response.MovieRatingId = query[0].MovieRatingId;
response.MovieRunTime = query[0].MovieRunTime;
}
List<MyMovieActor> actors = new List<MyMovieActor>();
foreach (MovieActor ma in query[0].MovieActors)
{
MyMovieActor a = new MyMovieActor();
a.ActorId = ma.ActorId;
a.ActorMovieId = ma.ActorMovieId;
a.ActorCharacter = ma.ActorCharacter;
a.ActorName = ma.ActorName;
actors.Add(a);
}
response.Actors = actors;
List<MyMovieMedia> medias = new List<MyMovieMedia>();
foreach (MovieMedia mm in query[0].MovieMedias)
{
MyMovieMedia med = new MyMovieMedia();
med.MediaId = mm.MediaId;
med.MediaMovieId = mm.MediaMovieId;
med.MediaFileLocation = mm.MediaFileLocation; //HDD Folder
med.MediaDefault = mm.MediaDefault; //Image to show on listing
medias.Add(med);
}
response.MediaFiles = medias;
}
return response;
}
So now I want to create
public List<MyMovie> GetMovies(string moviename, int movierating, string movieruntime)
Where all parameters will be populated or zero length strings. If they have a values I want to add that as part of the where clause.
so basically update
var query = (from oData in DbContext.Movies
where oData.MovieId == movieId
select oData).ToList();
to the equivalent of
var query = (from oData in DbContext.Movies
where 1=1
if(String.IsSafe(moviename))
{
&& oData.MovieName Like %movieId%
}
if(movierating != 0)
{
&& oData.MovieRating = movierating
}
if(String.IsSafe(movieruntime))
{
&& oData.MovieRuntime == movieruntime
}
select oData).ToList();
Looking into this has just confused me more, I have always writen my data layers using good old ADO.NET, DataReaders, Adapters, Stored Procs etc but with this project I have been asked to use EF. It is confusing me so much. What is it they say about old dogs and new tricks?!
Any help with this would be very much appreciated.
IQueryable is your friend here. It allows for composing the query in multiple steps, but it won't generate the SQL and execute it until it's needed (like due to iteration or a ToList() call). So your code would look like:
var query = (from oData in DbContext.Movies select oData);
if (!string.IsNullOrEmpty(moviename))
query = query.Where(m => m.MovieName.Contains(moviename));
...
这篇关于基于可选搜索参数选择数据使用实体框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
