solution on SP and EF & Ria Service

Question

As EF 4.0 released, more support to SQL server Stored procedure. Complex Type can be generated automatically for result dataset of SP. But complex type not support by Ria Service.

When I try to use ria service combined with EF 4.0, I want to get dataset by SP. This result is not mapped to any entity/table.

Some sulutions suggested by community are: 1. Create view to map sp result. (but for EF, if there is no ID or primary, can't be map to any entity) 2. Create Entity to map sp result.(for this, you even need to map SP for CRUD even though I don't need CUD on this sp result)

Also above solution can't be automated. As Database objects maybe changed with the time, auto update edm will lost above solution.

So what's the solution? I want to put data operation back to DB as possible, so that many changes can be done in DB and no need to rebuild .net assemblies(this cause redeploy).

Answer 1

We have the same problem come up many times in our development. The work around we chose to implement is WCF RIA Services with POCO (Plain Old CLR Object).

Using POCO's we can implement a domain service that communicates to the client by an object we create by hand. This gives us access to the database through any technology (EF, or anything really) on the server, allowing us to use the Stored Procedure and use of RIA on the client.

Of course, this process adds a funky step to the system. You'll have to maintain your POCO to your DB's Stored Procedure.

Simple Example:

[EnableClientAccess()]
public class FooBarService : LinqToEntitiesDomainService<MyDBEntities>
{
    public IQueryable<FooBar> GetFooBar()
    {
        var qry =   from FooBarSPs in this.ObjectContext.FooBarSPs
                orderby FooBarSPs.Name
                select new FooBar
                {
                    ID = FooBarSPs.ID,
                    Name = FooBarSPs.Name
                };
        return qry;
    }
}

If you must use WCF RIA Services and you want to talk to a stored procedure, using POCO objects as the messengers are the easiest way I've seen, to do it.

I recommend watching Brad Abram's talk at Mix 09 last year: http://videos.visitmix.com/MIX09/T40F