使用MVC Web Api进行ODATA和CRUD操作

I need to create a service (currently with ASP.NET Web Api) that is taking data from SQL database and exposes them using OData protocol. I'm extremely new to OData and Web Api.

我需要创建一个服务(目前使用ASP.NET Web Api),该服务从SQL数据库获取数据并使用OData协议公开它们。我对OData和Web Api非常陌生。

The problem is I don't know how to perform crud operations - I've looked through some samples available online but my case is a little bit more complicated (or maybe not, hard to say). I'm using Entity Framework.

问题是我不知道如何进行crud操作 - 我已经查看了一些在线可用的样本,但我的情况有点复杂(或者可能不是,很难说)。我正在使用实体框架。

Sometimes I need to do the "GET" with querying table directly but do "POST" with stored procedure. The biggest problem i have is limitation in parameters amount. As I understand I can have only one.

有时我需要直接使用查询表执行“GET”,但使用存储过程执行“POST”。我遇到的最大问题是参数数量的限制。据我所知,我只能有一个。

This is rather an issue in my case:

在我的情况下,这是一个问题:

public class ProjectsController : ODataController
{
    [EnableQuery]
    [EnableCors(origins: "*", headers: "*", methods: "*")]
    public IQueryable<GetProjects_Result> Get(string key)
    {
        var context = new OPMLVSQL001Entities();
        IQueryable<GetProjects_Result> results = context.GetProjects(key);
        return results;
    }

    [EnableQuery]
    [EnableCors(origins: "*", headers: "*", methods: "*")]
    public GetProject_Result Get(int projectId, string loginName)
    {
        var context = new OPMLVSQL001Entities();
        GetProject_Result result = context.GetProject(projectId, loginName).FirstOrDefault();
        return result;
    }

that code won't work - just to get all projects, I need to pass a login parameter. To get one entity, I need to use different method - and I have to pass two parameters. Which I don't know how to do in OData (I thought of a hack - passing json table in the only parameter I can use).

该代码不起作用 - 只是为了获得所有项目,我需要传递一个登录参数。要获得一个实体,我需要使用不同的方法 - 我必须传递两个参数。我不知道怎么做OData(我想到了一个hack - 在我可以使用的唯一参数中传递json表)。

So is there a way to use more than one parameter?

那么有没有办法使用多个参数?

I also have to implement a "create" method - an entity is being created with stored procedure again. What is the best way to follow in this case?

我还必须实现一个“创建”方法 - 再次使用存储过程创建一个实体。在这种情况下,最好的方法是什么?

One way probably is to add, Project entity to the project and do post like this:

一种方法可能是将项目实体添加到项目并像这样发布:

    [EnableQuery]
    [EnableCors(origins: "*", headers: "*", methods: "*")]
    public HttpResponseMessage Post(Project project)
    {
    }

but what if I have some logic in dbo.CreateProject([bunch of parameters]) that I need to run along and for that reason I'd like to run the stored procedure. Use values from projects to initialize dbo.CreateProjects parameters? I really don't know what's the best way to follow.

但是,如果我在dbo.CreateProject([参数串])中有一些逻辑,我需要运行,因此我想运行存储过程。使用项目中的值来初始化dbo.CreateProjects参数?我真的不知道最好的方法是什么。

1 个解决方案

#1

What exactly do you mean saying that you want to pass more than one parameter? OData definitely support complex queries with multiple filter parameters and much more.

你究竟想要传递多个参数是什么意思? OData肯定支持具有多个过滤器参数的复杂查询等等。

While making queries, OData allows you to define how exactly data should be retrieved, filtered and projected. You can use any number of parameters when filtering. For example, the following query string:

在进行查询时,OData允许您定义应该如何检索,过滤和投影数据。过滤时可以使用任意数量的参数。例如,以下查询字符串:

/people?$skip=10&$top=5&$select=FirstName, LastName&$filter=Address eq 'Redmond'

will be translated to something like

将被翻译成类似的东西

queryable
    .Where(p => p.Address == "Redmond")
    .Skip(10)
    .Take(5)
    .Select(p => new { p.FirstName, p.LastName });

Assuming that you are using Entity Framework, this query will be further translated into SQL database query.

假设您正在使用Entity Framework,此查询将进一步转换为SQL数据库查询。

#1