基于可选搜索参数选择数据使用实体框架 [英] Selecting Data Using Entity Framework based on optional search parameters

问题描述

我有一个项目，使用EF与数据库交互。它通过一个WCF Web服务来实现的。这是我第EF项目，我试图找出如何根据可选参数构造查询。

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.

我的Web服务有匹配字段的数据合同类EF。 Web服务wueries数据库，填充这些客户端类的传递回给客户端。举一个例子....

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....

在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> 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)...

我的Web服务方法....

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子句的一部分。

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.

所以基本上更新

                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();

展望这只是困惑我更多，我一直用好老的ADO writen我的数据层。 NET，的DataReader，适配器，存储的特效等，但这个项目我一直要求用EF。这是混淆了我这么多。难道他们说的老猎狗和新花样什么？

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的是在这里你的朋友。它允许构成在多个步骤中的查询，但它不会产生SQL和，直到它的需要（如由于迭代或了ToList（）调用）执行它。所以，你的代码将如下：

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屋！