EDMX database mapping two procedures to the same ComplexType

Question

I am creating an .edmx into my MVC project. I have mapped two procedures that returns movies information from database.

The first procedure, which is getMoviesByName, returns only Id and Name. The second procedure, which is getAllMovies, returns Id, Name and CreatedDate.

I would like to both procedures to returns data as a ComplexType called Movie.

I am instantiating my MyDbEntities and trying to use both procedures according to a condition just like in the example below but it fails when variable isLookingByName is true with the following message:

A member of the type, 'CreatedDate', does not have a corresponding column in the data reader with the same name.

Example:

var db = new MyDbEntities();
if(isLookingByName) 
{ 
    return db.getMoviesByName(name).ToList();
}

return db.getAllMovies().ToList();

So whenever the condition is true it throws that exception. The thing is that I cant change the first procedure to add CreatedDate.

Just to let you guys know in my View I display all those three information Id, Name and CreatedDate. When CreatedDate is null it is not displayed and I am fine with that.

Someone knows how should I proceed?

Thank you very much!

so there is no way you can include createDate even if its NULL — meda, Jul 02 '15 at 00:35
There is no alternative to use both procedures ? Some other type besides ComplexType or a workaround? Can I map to two ComplexTypes and then make they become only one model? — Roger, Jul 02 '15 at 00:46
this is a limitation of EDMX the thing is it creates all these xml fields for the objectresult, if you cannot return the same fields then the workaround is to use straight ADO.NET — meda, Jul 02 '15 at 00:47
could you please give me a link to learn how to use straight ADO.NET ? — Roger, Jul 02 '15 at 00:56

score 0 · Answer 1 · answered Jul 02 '15 at 01:00

The workaround is to use ADO.NET

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(
    "SELECT Id, Name and CreatedDate  FROM movies WHERE id= @id", connection))
    {

        command.Parameters.Add(new SqlParameter("@id", id));

        SqlDataReader reader = command.ExecuteReader();
        if(reader.HasRows())
        {
            var id= reader["Id"].ToString();
            var name= reader["Name"].ToString();
        }
    }
}

EDMX database mapping two procedures to the same ComplexType

1 Answers1