c# - 基于可选搜索参数使用 Entity Framework 选择数据

Question

我有一个使用 EF 与数据库交互的项目。它通过 WCF Web 服务执行此操作。这是我的第一个 EF 项目，我正在尝试研究如何基于可选参数构建查询。

我的 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> 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;
}

数据模型(简化)...

我的网络服务方法....

    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;
    }

所以现在我要创建

public List<MyMovie> GetMovies(string moviename, int movierating, string movieruntime)

将填充所有参数或零长度字符串的位置。如果它们有值，我想将其添加为 where 子句的一部分。

所以基本上更新

                var query = (from oData in DbContext.Movies
                         where oData.MovieId == movieId
                         select oData).ToList();

相当于

            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.NET、DataReader、适配器、存储过程等编写我的数据层，但在这个项目中我被要求使用 EF。这让我很困惑。他们怎么说老狗和新把戏？!

如有任何帮助，我们将不胜感激。

Answer 1

IQueryable 是您的 friend 。它允许在多个步骤中组合查询，但它不会生成 SQL 并在需要时执行它(例如由于迭代或 ToList() 调用)。所以你的代码看起来像:

var query = (from oData in DbContext.Movies select oData);
if (!string.IsNullOrEmpty(moviename))
  query = query.Where(m => m.MovieName.Contains(moviename));
...