Entity Framework 6 vs. Third-Party SPROC



  • This is critical to the question so please read the following bit in bold: I CAN NOT CHANGE HOW THE STORED PROCEDURE FUNCTIONS

    I'm working on an API that interfaces with a third-party database using its stored procedures, and I'm having trouble getting Entity Framework 6 to ... well, work.

    I added the data source to the project, created my .edmx file with the stored procedure added. The sproc returns (always returns) a single row with a single column labeled "COL1", containing a string. I need to call this sproc and use that string for following code.

    Here's the problem.

    An unhandled exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll

    Additional information: The data reader is incompatible with the specified 'dataModel.ThirdPartySPROC_Result'. A member of the type, 'COL1', does not have a corresponding column in the data reader with the same name.

    As far as I can tell, that error message is a damned lie. If I execute it manually in SSMS, it always returns COL1. If I execute it in bare ADO.net (without using Entity Framework), it always returns COL1.

    What's more, if I go into the Model Browser, double-click the sproc, and hit "Get Column Information", Entity Framework agrees with me that it always returns a column named "COL1." In fact, I generated that ThirdPartySPROC_Result class by having Entity Framework generate it for me.

    In short, as far as I can tell, I'm doing everything correctly, and there's still no worky. I'm completely stumped.

    Obviously, the most common cause for this error are:

    1. The sproc's returning column names are changed
    2. The sproc has conditional logic, where some conditions return a different table definition than others
    3. The sproc has conditional logic, where some cases may return no table result at all

    None of these apply to this sproc.

    I'm starting to think the problem is gremlins. AFAICT, there's no way to debug into the Entity Framework DLL and figure out exactly what is in the DataReader it's looking at to generate that error.

    Any ideas?



  • Have you tried to rename the column in sproc?

    ...

    Joking!

    Seriously, did you look into the obvious place?

    Aside from that, my first instinct is, there's some screwup with string encoding. You think the name is COL1, but it's really not.



  • @cartman82 said:

    Seriously, did you look into the obvious place?

    From the answer to that question:

    So you cannot use mappings when calling stored procedure.

    @cartman82 said:

    You think the name is COL1, but it's really not.

    But Entity Framework itself generated the non-working code.



  • Is it a legacy DB? Older SQL Server version? Foreign company, that might use some strange collation?
    I have no specific experiences with this, but I had problem with fucking stupid encoding in MySQL. Less likely in Microsoft-land, though.

    Could it be a bug in EF? Try telling nuget to set you up with EF5, just to test it out?

    BTW you could definitely get into the EF sources...... if you had resharper. :-)



  • @cartman82 said:

    Is it a legacy DB?

    No.

    @cartman82 said:

    Older SQL Server version?

    Kind of: 2008R2.

    @cartman82 said:

    Foreign company, that might use some strange collation?

    Collation on the DB is Latin1_General_BIN. Case-sensitive, but both the C# code and the Sproc have the column name in all-caps.

    @cartman82 said:

    Could it be a bug in EF?

    Possibly.

    @cartman82 said:

    Try telling nuget to set you up with EF5, just to test it out?

    No point to that, since the project has to use EF6. The workaround at this point is to run it using plain ol' ADO.net.

    @cartman82 said:

    BTW you could definitely get into the EF sources...... if you had resharper.

    I don't.



  • I completely removed the .edmx, regenerated it from scratch, same error happening. Googling doesn't suggest any conflicts between EF6 and the Latin1_General_BIN collation.



  • Have you tried regenerating the EF Model for the sproc? I know back when I did Silverlight working adding service references would sometimes FUBAR and I had to remove the reference, manually clean the generated files and regenerate. Serious PITA.

    Apparently EF Model first is really flaky with generation if this is any indicator.



  • This sproc returns only one column.



  • Why bother with two data layers? If the business logic is already in stored procedures then EF is just overhead. It's not like you can do linq-to-sql on a pile of procs. Just use a micro-orm like Dapper to run the proc and get the results in a format that linq is happy with.



  • I don't have the ability (well-- political capital) to change Entity Framework 6 or the third-party sproc. I definitely don't have the ability to add new third-party libraries. Like I said above, the current work-around is to use ADO.net directly to make this sproc call.

    I'm starting to think Entity Framework is just goddamned broken. This type of shit always happens to me whenever I have to use ORMs, I hate ORMs.



  • That's exactly what I would do with your limitations.

    BTW, I hate most ORMs. However, micro orms give me the code-generation and freedom from typographic errors I need while not generally annoying me.



  • That's great, please bug-off to another thread and talk about that to your heart's content.

    I'm here to solve the problem.



  • You already did - ADO.Net. Now, on to the tangents. We're twelve posts in and still on topic - that's unacceptable.



  • OK well I'm done for the day. Leave suggestions, I can try them tomorrow morning-- although I have a big meeting until noon or so, so I might not get to them until then.


  • kills Dumbledore

    I assume you've already verified this, but on the principle of starting with obvious and easy to overlook problems, have you verified that the data types of the columns are the same?

    The other thing that occurs is that maybe there are some non printable characters in the column name, even though that would both be a really stupid design and not explain why you can get the result without EF.

    Disclaimer: I've never used Entity Framework, so may be completely off the mark. If so, feel free to ignore me or blakeyrant at me.



  • Have you tried changing how the stored procedure functions?



  • @blakeyrat said:

    No point to that, since the project has to use EF6. The workaround at this point is to run it using plain ol' ADO.net.

    I was thinking just as a test if this is an EF regression. You can do it in a separate throwaway app.

    Other than that, I think you've sucked the large and powerful TDWTF .NET brain pool dry of ideas.


  • Discourse touched me in a no-no place

    @blakeyrat said:

    This type of shit always happens to me whenever I have to use ORMs, I hate ORMs.

    It's almost like it is applying the column selector to the wrong thing (e.g., against an auxiliary table) because someone fucked up and omitted some parentheses or something like that. One of these things where you stare at it a lot and still can't figure it out until you switch everything to excessively verbose and get creative about looking at the code it is really running (instead of what it claims to be running).

    I also hate ORMs.



  • @blakeyrat said:

    Like I said above, the current work-around is to use ADO.net directly to make this sproc call.

    Why would you use EF (or any other ORM) to call sprocs anyway? I'd drop down below the mapping level to do so, whether it be "raw" ADO.net (because EF is one of those impoverished ORMs that believes that the whole entire world is tables mapped to objects, or at least that's the impression this thread's giving me), or a more sophisticated DSL (such as the SQLAlchemy expression language). In fact, I'd write a CallStoredProcedure() that takes a sproc name and some params (assuming that C# varargs are sane), and spits back a resultset that the caller can peruse at will, just to wrap up any and all ADO.net gunk involved with a stored proc call into one, easy to modify place. :)



  • @tarunik said:

    In fact, I'd write a CallStoredProcedure() that takes a sproc name and some params (assuming that C# varargs are sane),

    That solves the wrong problem. If you do that, then you will make dependency tracking nearly impossible and will have loosely typed your parameters. Creating a simple wrapper function for exactly that one proc is a much better solution. To avoid creating a bunch of work, use some type of code generation technology that generates code based on the results of interrogating the stored procedure.

    I had to live with a version of exactly what you are describing a few years ago. The CallStoredProcedure method looked at the metadata for the stored procedure and matched method parameter 1 to sp arg 1, and so on. The end result was that if we ever added an argument to a stored procedure, we would have to change every call, even if the argument had a default value. It also made calls to procs with many arguments confusing to read.

    EF actually does this, but comes with a bunch of baggage. A T4 template would be the ultimate home-grown low-level solution. Many .Net micro-orms are simply a well-tested T4 template.



  • @Jaime said:

    That solves the wrong problem. If you do that, then you will make dependency tracking nearly impossible and will have loosely typed your parameters. Creating a simple wrapper function for exactly that one proc is a much better solution. To avoid creating a bunch of work, use some type of code generation technology that generates code based on the results of interrogating the stored procedure.

    A runtime HLF of that nature could work in a suitable language, yes; please do not suggest actual code generation though. (BTW: "sane" varargs are not loosely typed.) Also, what do you mean by "dependency tracking"?

    @Jaime said:

    I had to live with a version of exactly what you are describing a few years ago. The CallStoredProcedure method looked at the metadata for the stored procedure and matched method parameter 1 to sp arg 1, and so on. The end result was that if we ever added an argument to a stored procedure, we would have to change every call, even if the argument had a default value. It also made calls to procs with many arguments confusing to read.

    The CallStoredProcedure() is basically a low-level tool to get the job done: it wouldn't go nearly as far as you're talking about in terms of introspecting the stored procedure.



  • @tarunik said:

    Why would you use EF (or any other ORM) to call sprocs anyway?

    I wouldn't. I just said a few posts ago that I hate ORMs. I'm working for an employer who is mandating EF6 be used for database interactions.

    @Jaime said:

    EF actually does this, but comes with a bunch of baggage.

    In theory it does, my experience is quickly showing me it doesn't fucking work.



  • @blakeyrat said:

    cartman82 said:
    Try telling nuget to set you up with EF5, just to TEST it out?

    No point to that, since the project has to use EF6. The workaround at this point is to run it using plain ol' ADO.net.

    Keyword: - test. Might help you narrow it down. Might give you the political capital to not use EF6.

    YMMV

    (For all I know it may be just as easy to just go ahead and do it in ADO as it is set up EF5).


    Filed under: a sincere suggestion.



  • There's a second option I thought of in the shower.

    I could wrap this sproc in a custom one we create which can (or can be modified to) work with EF6. I'd have the ability to change column names or whether it returns values or what-not in the custom one.

    However, that's a lot more WTF-y than the ADO.net route.



  • @tarunik said:

    Also, what do you mean by "dependency tracking"?

    Answering the question "What will be affected if I change this proc?".

    There are a ton of good tools to do this and they will all be rendered useless if all proc calls go through the same method call.

    @tarunik said:

    BTW: "sane" varargs are not loosely typed

    Can you please give me an example of a language that can strongly type varargs.

    @tarunik said:

    The CallStoredProcedure() is basically a low-level tool to get the job done: it wouldn't go nearly as far as you're talking about in terms of introspecting the stored procedure.

    That's why I said "a version of". But, even if you don't make that mistake, the very concept of a single CallStoredProcedure method removes more value than it adds.


  • I survived the hour long Uno hand

    I know nothing about EF6. But when I'm trying to debug weird libraries making calls to stored procs, I usually set up a trace on the dev SQL server to see what it's passing in and getting back. Have you done that? I see that you verified what it ought to be doing to call the proc, but did you verify what it actually does?



  • @blakeyrat said:

    I could wrap this sproc in a custom one we create which can (or can be modified to) work with EF6

    I've had good luck with creating a table-type variable, dumping the output of whatever I'm doing to the variable, and then using SELECT COL1 FROM @var as the last line of the procedure. A lot of tools get confused trying to determine the output of stored procedures that have logic in them, this sometimes helps.

    If they won't let you create another stored procedure, just make an ad-hoc command with the text:

    DECLARE @var TABLE (COL1 varchar(666)); INSERT @var EXEC myproc; SELECT COL1 FROM @var;



  • @Jaime said:

    Can you please give me an example of a language that can strongly type varargs.

    C++11 (thanks to varadic templates), if you are a [s]pedant[/s]drooling idiot about your definition of 'strong typing'. Strongly-typed dynamic languages have been doing this properly ever since Lisp...



  • @Jaime said:

    Answering the question "What will be affected if I change this proc?".

    There are a ton of good tools to do this and they will all be rendered useless if all proc calls go through the same method call.

    So, they're worth less than grepping/Ctrl-F'ing the codebase for the name of the stored procedure? Funny idea of a tool you have there...



  • Some disassembled source that might assist:

    private static int GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary<string, FunctionImportReturnTypeStructuralTypeColumnRenameMapping> renameList)
    {
      string renameForMember = ColumnMapFactory.GetRenameForMember(member, currentType, renameList);
      int ordinal;
      if (!ColumnMapFactory.TryGetColumnOrdinalFromReader(storeDataReader, renameForMember, out ordinal))
        throw new EntityCommandExecutionException(System.Data.Entity.Resources.Strings.ADP_InvalidDataReaderMissingColumnForType((object) currentType.FullName, (object) member.Name));
      else
      return ordinal;
    }
    
    private static bool TryGetColumnOrdinalFromReader(DbDataReader storeDataReader, string columnName, out int ordinal)
    {
      if (storeDataReader.FieldCount == 0)
      {
        ordinal = 0;
        return false;
      }
      else
      {
        try
        {
          ordinal = storeDataReader.GetOrdinal(columnName);
          return true;
        }
        catch (IndexOutOfRangeException ex)
        {
          ordinal = 0;
          return false;
        }
      }
    }
    

    The only bit stands out as possibly odd is :

    if (storeDataReader.FieldCount == 0) 
    

    Unless of course the reader has no fields...



  • @tarunik said:

    So, they're worth less than grepping/Ctrl-F'ing the codebase for the name of the stored procedure? Funny idea of a tool you have there...

    No, Ctrl-F is nearly worthless if you are looking for transitive dependencies. The tools are awesome, but if you have a single CallStoredProcedure method, then the tool will simply show you that everything is dependent on that method without answering the real question.



  • @Yamikuronue said:

    I know nothing about EF6. But when I'm trying to debug weird libraries making calls to stored procs, I usually set up a trace on the dev SQL server to see what it's passing in and getting back. Have you done that?

    That's like a week-long wait. Don't underestimate the WTF around here.

    @Jaime said:

    DECLARE @var TABLE (COL1 varchar(666)); INSERT @var EXEC myproc; SELECT COL1 FROM @var;

    Wow, now that's WTF-y.

    @null_loop said:

    Some disassembled source that might assist:

    Thanks, but I think I actually got to that level. EDIT: or maybe not. My biggest problem was that I couldn't work-out how to get the debugger to show me the actual data in DbDataReader after it attempts to call the sproc.



  • @blakeyrat said:

    Wow, now that's WTF-y.

    Yup. But sometimes it works. Consider this proc:

    CREATE PROC MyProc (@type int)
    AS
    IF @type = 1
      SELECT TOP 1 COL1 = FirstName FROM Employees
    ELSE
      SELECT TOP 1 COL1 = LastName FROM Employees
    
    

    If FirstName and LastName have different max lengths, then the schema of the returned result set is ambiguous. The more logic there is, the more ambiguity there is to the result set. My horrible hack enforces a simple schema on the returned data.



  • Here's the report I wrote:

    Inserting a case requires use of one XXX stored procedure, XXX. Due to being a third-party sproc, we have no ability to modify it.

    This sproc has two problems which prevent it from being used cleanly with Entity Framework 6 (EF6):
    • The sproc specifically checks its own transaction level, and throws an error if it is run inside a transaction. There is no way to use this sproc inside a transaction, whether the transaction is started in SQL, C#, or another sproc.
    • The sproc returns results in a table with one row and one column, named “COL1”. The code generator in Entity Framework is unable to create a class that can access this sproc’s results.

    There are two possible workarounds to solve these issues:
    • The C# code to call this sproc can be written using ADO.net directly, instead of through EF6.
    o Advantages: we can use the same connection string and connection pool as EF6, reducing code duplication
    o Disadvantages: the solution is no longer “pure” EF6, possible impact to ease of unit test authoring
    • Since we have the ability to create our own sprocs on the server, we can create our own sproc to “wrap” the XXX sproc and reformat its output in a manner that works with EF6.
    o Advantages: the C# solution remains “pure” EF6
    o Disadvantages: the sproc would have to be deployed in the project and becomes another moving part that needs to be maintained

    My recommendation is to use the first solution.

    Not final yet, so suggest edits.


  • Java Dev

    Let me add a mental note to the 'Things Oracle hasn't caused me headaches with' column.



  • @blakeyrat said:

    Not final yet, so suggest edits.

    You forgot a link to this thread, to prove you've tried.


  • Discourse touched me in a no-no place

    @cartman82 said:

    You forgot a link to this thread, to prove you've tried.

    Oh ho ho ho ho ho, now that's funny.



  • Really stupid question:

    Are you ensuring that you're not getting back counts of affected rows from SQL server?

    IE:

    set nocount on
    exec sproc
    


  • What does that have anything to do with anything?

    Are you suggesting Entity Framework actually barf if you have a setting on the connection (one it doesn't even expose, BTW) set wrong?



  • set nocount on is a transaction session command that changes affected rowcount from being returned directly before the actual dataset is returned. Often I've found many systems barf because they interpret the number of affected rows as the actual return data and get into a really weird state of column headers/content data vs actual data. it's something you'd run as part of the sql statement / directly before/ as part of the transaction / as part of the command details (depends entirely on how you're querying the data)

    Might not apply in your case, but it's usually a 2 second check to make sure it's not the problem.



  • You do realize Entity Framework is an ORM, right?



  • Yes



  • Don't think it'll be the row count - but some metadata certainly seems to be missing



  • The best advice I can give you, and I'm talking out of pure experience built up from hundreds of hours, don't use EF6.

    Forget about EF6 when dealing with something that just needs a little bit of logic applied to it.

    Use either the good old approach ADO.NET or a MicroORM that can do the mapping for you. I can assure you that the time spent writing either ADO.NET or throwing a bit of investigation after a MicroORM are well spent and you will reach your milestones much quicker and without any blackbox magic :)



  • I don't even work there anymore.

    Even Atwood's "you're necro-ing an old thread" warning is buggy and useless. Like everything he writes.


Log in to reply