Dailycode.info

Short solution for short problems

EF: add new Migration when there are multiple migration configurations in your project

You could get this error:

More than one migrations configuration type 'Configuration' was found in the assembly 'BDO.DataLayer.MSSQL'. Specify the fully qualified name of the one to use.

And then look for a while for a solution. You'll find that you need to tell what configuratin type to use, but not so easy to find is how to get this name.

So extend the add-migration command like this (Verbose parameter shows the SQL that is being rendered):

Add-Migration "VATRefundExtraProperties" -ConfigurationTypeName "DataLayer.MSSQL.Migrations.Configuration" -Force –Verbose
And after update the database:

update-database -ConfigurationTypeName "DataLayer.MSSQL.Migrations.Configuration" -Force
You can also add the Verbose flag here if you like.

More info on the attributes for ?-migration:


How to get a dataset with variable SQL from the entity framework

When you are working with the EF, you will finally get to the point that you need some joined data from the database that could be based on variable SQL queries. In our case we can define SQL queries in the database that will decide what to show in grids. Since this data is combining several tables from a non relational DB, its not returning entity data. So I found a good way to get a dataset and still use the entity framework connection. So no new connection logic to be instantiated:

 

public DataSet ExecuteStoreQuery(string commandText)

{

    DataSet retVal = new DataSet();

    EntityConnection entityConn = (EntityConnection)MyContext.Connection;

    SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;

    SqlCommand cmdReport = new SqlCommand(commandText, sqlConn);

    SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);

    using (cmdReport)

    {

        cmdReport.CommandType = CommandType.Text;

        daReport.Fill(retVal);

    }                  



    return retVal;

}

 

You should only use this for view purposes only!!!


Addressing tables that are not in the EDMX model.

When you are working with the entity framework, it’s no problem to use non referenced tables that belong to the same database. By using the ExecuteStoredQuery function, you can provide an SQL script. Keep in mind that the returned data needs to be mapped to your entity. Here a small example:

public Clients GetClientForPlant(string plantID)

{

    return Ents.ExecuteStoreQuery< Clients>("select * from Clients c inner join Plants p on c.Client = p.Client and p.Plant = @p0", new SqlParameter() { ParameterName = "p0", Value = plantID }).First();

}