1

I would like to use a class example:

public class ClassA
{
     public int ID {get;set;}
     public string Name {get;set;}
}

as a parameter object to be passed to a stored procedure.

this would be a sample of my call

List<ClassB> result = this.Database.SqlQuery<ClassB>("GetSomeList", ClassA).ToList<ClassB>();

The parameter names in the stored procedure would match the properties of "ClassA".

If I remove the parameters object (ClassA) the call works fine and returns correct results. I would prefer to not use Entityframework designer or add any DB attributes to my entities as they are reusable components. Also if this parameters object can be used to hold Output parameters that would be nice as well. I haven't found any good documentation for using POCO as a parameters object.

This is with EntityFramework6 MVC5

Dave Schweisguth
  • 36,475
  • 10
  • 98
  • 121
eBot
  • 117
  • 1
  • 2
  • 10
  • As far as I know, you can not do that. At least I don't believe I've ever seen it done. You could create an extension method on ClassA, and that likely would be your best approach. – Robert McKee Jun 08 '15 at 18:40
  • I have a whole library of objects and would prefer a different method. Is there any way to Execute a stored procedure using EF as the ORM and a POCO parameters object other than my current usage defined in the original post? – eBot Jun 08 '15 at 18:46
  • You can with EF create stored procedures. Still won't take a class for parameters however. Then you can `db.GetSomeList(ClassA.Id,ClassA.Name)`. Perhaps there is a way if you created/imported the stored procs into your dbContext you could use reflection in an extension method. I haven't seen any, but I don't see why you couldn't. – Robert McKee Jun 08 '15 at 18:54
  • That seems to be more of a workaround. Is there a better ORM to use? Or should I look into using entity framework in its more "normal" state. – eBot Jun 08 '15 at 19:18
  • I wouldn't say that is a major limitation of the ORM. Just import the stored procedures and call them. It isn't likely to save you anything since you have to manually specify the name of the proc in your call, and it would seem like a lot of reflection to call a stored proc would be a decent performance hit. – Robert McKee Jun 08 '15 at 19:25
  • So I either go the way of manually typing out each of my parameters or bloating my project with edmx and importing and having the designer generate everything. So when something changes I have to re-import. It seems like either way I am constrained by the designer or extensive coding. – eBot Jun 08 '15 at 20:27
  • You could always create a t4 template that generates the necessary stuff for you as well. (It is what the edmx designer does anyhow). You wouldn't need the entire edmx then at least. – Robert McKee Jun 08 '15 at 20:52
  • Thanks ill look into that as a possibility. – eBot Jun 08 '15 at 20:59

1 Answers1

0
public static class DbExtensions {
  public static object[] ToObjectArray(this ClassA ca) {
    return new object[]{ca.ID,ca.Name};
  }
}

List<ClassB> result=this.Database
  .SqlQuery<ClassB>("GetSomeList", ClassA.ToObjectArray())
  .ToList<ClassB>();
Robert McKee
  • 21,305
  • 1
  • 43
  • 57