Generate list view from stored procedure using MVC contextual menus and EF

Question

It began with a SQL Server stored procedure that looks like this :

CREATE PROCEDURE [dbo].[ExtractionPourFicheSante]
    @AnBudg NVARCHAR(4),
    @Ecole NVARCHAR(3),
    @Fiche NVARCHAR(10),
    @grprep NVARCHAR(20),
    @classi NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    IF (ISNULL(NULLIF(LTRIM(RTRIM(@Fiche)), ''), 'NA') = 'NA')
        IF (ISNULL(NULLIF(LTRIM(RTRIM(@grprep)), ''), 'NA') = 'NA')
            IF (ISNULL(NULLIF(LTRIM(RTRIM(@classi)), ''), 'NA') = 'NA')
                SELECT
                    ISNULL(z.FICHE, '123456789') AS TheKey,
                    z.*
                FROM  
                    [gpi].dbo.GPM_E_ELE_Z_DOSSIER Z
            ELSE
                /* similar query */
        ELSE
            /* similar query */
     ELSE
         /* similar query */
END

Then bringing it into an ASP.NET MVC project with EF 6.0 database first approach.

Brought the stored procedure in with auto-mapper by right clicking in the EDMX file.

Now trying to create a list type view using the contextual MVC menu, with ExtractionPourFicheSante_Result as the view model is actually returning an error :

'ExtractionPourFicheSante_Result' has no primary key defined. Define the key for this Entity type.

Well OK, the best candidate for being a primary key is TheKey which I know for sure is UNIQUE. Searched through with google and on SO... Haven't found anything about defining a primary key in a stored procedure. Neither found anything about getting EF to pretend there is one. I could work this around as usual. But I've had enough...

This may be impossible with the current version of EF. It's just weird that they allow to import such a stored procedure and then one can't work with it because there is no primary key involved in the select part.

Any clean solutions ? Someone have been through this ? Thanks a lot for reading.

Try adding the `[key]` attribute to that field in the model class. — Crowcoder, Aug 13 '18 at 17:15
Without taking into account problems with EF themselves, this SP body does not look fine at all. — Ivan Starostin, Aug 13 '18 at 17:19
@Crowcoder LOL... it's DB first so it's written right above every generated class something like : Changes can produce unexpected behavior, Changes will be overwritten when generating. So I first tried to make a partial class and define the key inside. Sadly, it didn't work, so I went to put it right where they say it's useless to make any changes for above reason... the view created itself fine. I suppose I won't have any problems until I regenerate my model, that's when the `[Key]` disappears... — Antoine Pelletier, Aug 13 '18 at 17:46
You might be able to add it with the fluent API instead of annotations - not sure. Also consider not using EF to call stored procedures. Dapper is easy and lightweight if you want to use an abstraction above ADO.Net. — Crowcoder, Aug 13 '18 at 17:52
@IvanStarostin Maybe yes, but the important thing about the SQL part is : Can i define a primary key inside the select of a stored procedure ? — Antoine Pelletier, Aug 13 '18 at 17:57
@AntoinePelletier no, these aspirations make not much sense in context of SP. — Ivan Starostin, Aug 13 '18 at 18:02
Can you explain what this i smeant to do `(ISNULL(NULLIF(LTRIM(RTRIM(@Fiche)), ''), 'NA') = 'NA')`? Is it just checking that @fiche has a value? — TomC, Aug 14 '18 at 05:23
@TomC yes, it checks if Fiche is null OR empty string OR white spaces — Antoine Pelletier, Aug 15 '18 at 13:06
Do you see `ExtractionPourFicheSante_Result` under complex types in the model browser? Complex types shouldn't need to have primary keys. — Gert Arnold, Aug 16 '18 at 19:11
@GertArnold Yes, And that's all true, but when you want to "auto-create" MVC views from the contextual menu (i use that a lot, it's saving me a LOT of time) a window pop-up and tells you there is no primary key defined in your class... If it was a `edit` or `create` view... it would require a primary key yes. But it's ONLY a `list` view so I don't REALLY need it. — Antoine Pelletier, Aug 16 '18 at 19:40
Have you try using a Fake key and creating classes only for those SP ?( https://stackoverflow.com/questions/20970416/using-stored-procedure-in-entity-framework) By the way it´d maybe sound weird, but always remember that you can use the SP with the used of ADO .NET. Hope this helps you. — Sergio Rezende, Aug 16 '18 at 19:55

Antoine Pelletier · Accepted Answer · 2018-10-02T19:20:47.700

In the generated model class (MVC, C#, generated by entity), two line were TEMPORARILY added :

//------------------------------------------------------------------------------
// <auto-generated>
//------------------------------------------------------------------------------

using System.ComponentModel.DataAnnotations;               // THIS ONE

namespace Rapport_CE_MVC.Models
{
    using System;

    public partial class ExtractionPourFicheSante_Result
    {
        [Key]                                              // AND THIS ONE
        public string fonds { get; set; }

        /* all the rest of the properties */
    }
}

By adding [Key] keyword, it simulate a primary key, you will need to copy paste the using above though.

Setting these correctly will allow you to use View creators using MVC contextual menus just like you would for normal tables (it worked for list, not sure about edit and others).

Once the view has been auto-created, know that regenerating the model will erase those 2 lines above, but the created view remains and will be fully functional whether the Key is set or not (works for list type).

score 0 · Answer 2 · answered Aug 20 '18 at 13:58

0

Not sure but try using row_number in select statement

                SELECT
                    ISNULL(z.FICHE, '123456789') AS TheKey,
                    z.*,
                    row_number() over (order by z.createddate desc) rowID 
                FROM  
                    [gpi].dbo.GPM_E_ELE_Z_DOSSIER Z

Make sure you have ComplexType and FunctionImport generated for your proc. Also make sure to save edmx file regularly.

answered Aug 20 '18 at 13:58

AnkitG

46
4

It's a good way to have a unique row number, but MVC still doesn't like it when it comes to generating a `list view` automatically using visual studio contextual menu – Antoine Pelletier Aug 22 '18 at 14:43

Generate list view from stored procedure using MVC contextual menus and EF

2 Answers2