Showing posts with label entity framework. Show all posts
Showing posts with label entity framework. Show all posts

Wednesday, 5 August 2020

Entity Framework Core with Existing Database

Entity Framework Core only supports code first database aproach. 
To use existing database, we'll need to reverse Engineer Model from existing Database. 
  • Open Visual studio.
  • Menu > tools >Nuget Package Manager >
  • open PM Console
Note: All the tables in the database must have Primary key defined. else you'll get error like:
Unable to generate entity type for table 'dbo.xOrdersMBS'.
Unable to identify the primary key for table 'dbo.xActivePricesEPB'.
Unable to generate entity type for table 'dbo.xActivePricesEPB'.
Unable to identify the primary key for table 'dbo.xLeadTimePBOM'.
Unable to generate entity type for table 'dbo.xLeadTimePBOM'.


 In the console, run the below power shell commands:

Using Windows Authentication:
Scaffold-DbContext "Server=DatabaseServerAddress;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

Using SQL Account:
Scaffold-DbContext "data source=DatabaseServerAddress;initial catalog=DatabaseName;uid=UserName;pwd=Password;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

Note: -f force create the files if the file exist in the folder Models.

Thursday, 2 July 2020

EntityFramework Insert error due to PrimaryKey



MyProject.Controllers.CurrencyCodesController Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.
System.Data.Entity.Infrastructure.DbUpdateException: Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation. ---> System.Data.Entity.Core.UpdateException: Unable to update the EntitySet 'CurrencyCodes' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.


Scenario: 
The error occured while trying to insert the currencycodes entity to the database. 
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "cucCurrencyCode,cucDescription,cucMultiplier,cucRate,cucInUse")] CurrencyCode currencyCode)
{ if (ModelState.IsValid)
{ try
{ db.CurrencyCodes.Add(currencyCode);
db.SaveChanges();
}
 catch (Exception ex) {
log.Error(ex.Message, ex);
}
 return RedirectToAction("Index");
} return View(currencyCode);

}



Solution: 
1) Check Primary key in .edmx file.  In my case cucInUse was set as Entity key. I removed it.
2) Now Right click the .edmx file and click "Open with...". Select XML (text) Editor
3)  Find the element <EntitySet name = "CurrencyCodes"...>
4) Change store:Schema="dbo" => Schema="dbo"
5) delete element <DefiningQuery>
6) Clean the project. 
7) close Visual studio.
8) Open visual studio. Open the project. 
9) Clean and build the project. 
10) Run the application.

How to find first weekday in the month in MSSQL

declare @n datetime = dateadd(mm,0,getDate())    declare @FirstWeekDayOfMonth int    SELECT @FirstWeekDayOfMonth= CASE          WHEN Dat...