Showing posts with label Entity Framework. Show all posts
Showing posts with label Entity Framework. Show all posts

Wednesday

How to avoid duplicate records in SQL view using Entity Framework

Introduction

Sometimes while using SQL we experience that SQL query returns same or duplicate results. Since we expect and need a single result from the query, it is a problem. Here we will discuss what causes duplicate records and how to avoid them in SQL view using Entity Framework.

You can also know about how to Fix SQL error: 26 - A network-related error occurred in SQL Server by reading my previous article.

Duplicate rows in Entity Framework SQL View 

In Entity Framework primary key is known as entity key. Entity Framework auto set fields as entity key for those not null column and return the row that match those entity key that causes the problem.
The problem is on the application side Entity framework mapping, as object mapping rules in Entity framework are different with database. The Entity framework does the mapping on application memory with accordance to its designed logic when the query results returns from database. Entity Model has a unique way of handling rows with equal primary key values. To fix issue duplicate records in SQL view using Entity Framework, you need to write below code:
 How to avoid duplicate records in SQL view using Entity Framework

You can set AsNoTracking option directly on your view to resolve this issue.

context.myviewname.AsNoTracking().Where(x => x.ColumnName != null);

Conclusion

To avoid this problem the primary key columns should be chosen correctly. If primary key couldn't be chosen correctly due to null-values, we add a column to our view that always have a unique value for each row and make it primary key in entity model and set AsNoTracking. This is how explained  How to avoid duplicate records in SQL view using Entity Framework.

Relevant Reading

Thursday

Entity Framework: Store update insert or delete statement affected an unexpected number of rows (0)

Introduction I have already explained very known an entity framework error Validation failed for one or more entities, today providing a resolution of another entity framework error. Yesterday, I was working on MVC application using Entity Framework, meanwhile I was setting up my code then a new error comes off me "Store update, insert, or delete statement affected an unexpected number of rows". After my deep study, then came up a perfect solution to remove this issue permanently from my solution. I have explained all steps as given below.

Error Description

Store update, insert, or delete statement affected an unexpected number of rows (0).
Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Store update insert or delete statement affected an unexpected number of rows (0)
Fix Entity Framework Error

Steps to Remove Error [Store update, insert, or delete statement affected an unexpected]

Set update mode to add, it was earlier set to modified state using entity framework. If table primary key greater than zero then it will modified otherwise it will add a new record in database table.

Wrong Statement

I had written given below wrong statement to add a new fresh record into my database table record, it was wrongly added state to Modified.
_context.Entry(model).State = EntityState.Modified;

Corrected Statement

Now, I corrected this statement, I just checked that primary key contains a value means a record need to update otherwise primary key value 0 means my database table needed to add a new fresh record in to my database table record.

if (model.TablePK > 0)
{
   _context.Entry(model).State = EntityState.Modified;
}
else
{
   _context.Entry(model).State = EntityState.Add;
}

Conclusion

While we coding seriously in our app then suddenly can trap up in few unexpected issues i.e. Store update, insert, or delete statement affected an unexpected. I have provided complete to recover unexpected issue. Hope it will help to all who eventually got in this issue i.e. Store update, insert, or delete statement affected an unexpected.

Suggested Reading

Saturday

MVC ModelState: ModelState Return always false using MVC

Introduction

I am today explaining one more very nasty error about ModelState Error. We daily work on MVC Apps using Model, sometime we get ModelState returning always false at that time, it is more required to diagnose our errors in shot period and take immediately action to remove our ModelState errors.

Thursday

Dropdown List: Cascading Dropdown list in MVC

Introduction

Today, I am explaining about cascading drop down list in MVC, while we are working on CRM, ERP, Web Apps or other web apps then we often require to populate cascading dropdown list e.g. First populate Country then select country from list then corresponding state will be populated in other dropdown. This is the way that how we need cascading dropdown list in our MVC applications. Here I am taking example, first populating drop down list of classes, on the click of classes, the other drop down list will be populated of Subjects and then while we click on Subjects then chapter will be populated correspondingly.

Add Entity Framework Model in MVC Project

First of all, you have to add entities in our project to populate cascading drop down in our project. I am providing a video link (Add Entities) which explains all steps how to add entities in our project using entity framework.

namespace MvcApplication1.Context
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    public partial class MVC_AppEntities1 : DbContext
    {
        public MVC_AppEntities1()
            : base("name=MVC_AppEntities1")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet tblChapterMasters { get; set; }
        public virtual DbSet tblClassMasters { get; set; }
        public virtual DbSet tblSubjectMasters { get; set; }
    }
}

Populate First Dropdown List (Subjects)

Here I have written an action method in controller as below:
[HttpGet]
public ActionResult My_MVC_App(string ddlClasses, string ddlSubjects, string ddlChapters)
{
    var _context = new MVC_AppEntities1();
    List _classlst = GetClasses();

   // Clases
   List li = new List();
   foreach (var item in _classlst)
   {
   li.Add(new SelectListItem { Text = item.ClassName, Value = item.ClassIDPK.ToString() });
   }
   ViewData["Classes"] = li;

   return View();
}

Populate Second Dropdown List (Subjects)

Next step is to write action method to populate second dropdown list of subject while we click on classes drop down list.
[HttpGet]
public JsonResult GetSubjectsNew(Int16 ClassID)
{
    List _lstSubjects = GetSubjectsID(ClassID);
    List _liSubj = new List();
    /// 
    var _context = new MVC_AppEntities1();
foreach (var _itemsubject in _lstSubjects)
{
  _liSubj.Add(new SelectListItem { Text = _itemsubject.SubjectName, Value = _itemsubject.SubjectIDPK.ToString() });
}

return Json(new SelectList(_liSubj, "Value", "Text"), JsonRequestBehavior.AllowGet);
  

Populate Third Dropdown List (Chapter)

Now next step is to populate third dropdown list of chapters while we click on subjects correspondingly.

 [HttpGet]
 public JsonResult GetChapterList(Int16 SubjectID, Int32? ChapterID)
{
   List _lstChapters = GetChapters(SubjectID);
   List _liSubj = new List();

   foreach (var _itemsubject in _lstChapters)
   {
     _liSubj.Add(new SelectListItem { Text = _itemsubject.ChapterName, Value = _itemsubject.ChapterIDPK.ToString() });
   }
   return Json(new SelectList(_liSubj, "Value", "Text"), JsonRequestBehavior.AllowGet);
}

Helping Method to Populate All Dropdowns (Classes, Subjects, Chapters)

Helping Methods

I have explained below three helping methods to populate all drop downs correspondingly.

MVC Razor View
Now I am talking about MVC razor view where we are playing with all three drop downs interface.

Import Jquery Library in our Project (Under Head Tag)


Head Tag

MVC Razor HTML Helpers

Below is written code to provide complete interface to populate three dropdowns.

razor view

Jquery Ajax call to MVC Controller

Here is written a Ajax/Jquery method to call Controller action for for subjects

Class Jquery Ajax Call

Here is written other Ajax/Jquery method to call Controller action for the chapters as below:

Chapter Jquery Ajax Call

Conclusion

Explained above about cascading dropdown in MVC with concerned every aspect of the MVC apps. Cascading dropdown is the common requirement of our each MVC applications, if we skip few steps to cascading drop down then sometimes it waste our very precious time of development.

Development Environment

Visual Studio 2013, Entity Framework 6.0, Database script under app folder App_Data, Change web.config settings according to your machine specific, Data source, Initial Catalog, Database User ID, Password etc.

Download Complete Project

Download

Relevant Reading

Sunday

Entity Framework Error: Compiling transformation metadata file entity framework

Introduction

Compiling transformation metadata file entityframework.dll could not be found, this issue turns out while our answer indicating entityframework.dll on wrong area. It needs correct to right location. I have clarified all strides one by one after way. Prior I have as of now clarified substance system mistake. Earlier I have already explained entity framework error Store update insert or delete statement affected an unexpected number of rows (0).

Error Description

Error 1 Compiling transformation: Metadata file 'C:\Program Files\Microsoft Visual Studio 12.0\Common7\IDE..\IDE\EntityFramework.dll' could not be found F:\EF\WebApplication1\WebApplication1\Model1.tt 1 1 WebApplication1

Saturday

Exception: System Data Entity Infrastructure DbUpdateException

Introduction

I was engaged on MVC application utilisation Entity Framework, MVC and Jquery. I hit the Save button to save corresponding values into database, however this did,'t happen fine as common, I got the special error An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code.

Error Description

An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code

Solution I

When I started operating to resolve issue, debugging deeply and found of stack trace message then I found that this issue was because of database table identity off.

Table Identity off

I simply set table Identity on and once more tried to save relevant values into database, this time it worked fine for me.
Table Identity On


Solution II

This Error A special case of sort An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code yet was not took care of in client code likewise comes when duplicte value entering in primary key so you have to cross check to conquer this mistake.

Need to Refresh Entity Framework Model After Database Change

Once you set primary key in database then when you would like to refresh your entity framework model through front end code.

Conclusion

Another superb expertise of MVC, Entity Framework, Jquery application. Every time, in each new application usually comes new problems. From these issues, we learnt another lesson each time and smoothing approach to next time. Above, I have clarified in subtle element by what method would we be able to determine issue of A special case of sort An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code. In the event that despite everything you locate the same issue, kindly keep in mind to think of me here.

Suggested Reading

Sunday

Error CS1963: An expression tree may not contain a dynamic operation

Introduction

I was working on a MVC Application (Razor Engine) using with Entity Framework. I triggered an error not able to populate entities while binding the controls with the database. I have written below detail of error as follows:

Error Description

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: CS1963: An expression tree may not contain a dynamic operation

Saturday

Entity Framework Examples: Entity Framework Code First Examples

Introduction

Entity Framework is an ORM (Object Relational Mapper) which enables to developer cut off code lines. ADO.Net Entity Framework is now every C#/VB apps necessity to use in MVC, ASP.Net, and Windows applications etc. Here is explained operations with code first approach.
Main Operations (CRUD) in Entity Framework is demonstrated below with code snippet.

Pre-Requisites

If you need these snippet into your machine then require following VS 2012/2013, .Net Framework 4.5 and MS SQL Server.

Installing EF NuGet Package

Right click on Reference folder of your respective project to install Entity Framework NuGet Package and select "Manage NuGet Packages"

Installing through Package Manger Console

Alternatively, you can install Entity Framework  through Package Manger Console.
PM> Install-Package EntityFramework

Download Latest Entity Framework

Here is an official link https://www.nuget.org/packages/EntityFramework/ where you can download latest Entity Framework package.

Entity Framework Version History

Here is the complete story of all Entity Framework Versions. Top most in the list are latest versions.

EntityFramework 7.0.0-beta4
EntityFramework 6.1.3 (this version)
EntityFramework 6.1.3-beta1
EntityFramework 6.1.2
EntityFramework 6.1.2-beta2
EntityFramework 6.1.2-beta1
EntityFramework 6.1.1
EntityFramework 6.1.1
EntityFramework 6.1.0
EntityFramework 6.1.0
EntityFramework 6.1.0
EntityFramework 6.0.2
EntityFramework 6.0.2
EntityFramework 6.0.1
EntityFramework 6.0.0
EntityFramework 6.0.0-rc1
EntityFramework 6.0.0-beta1
EntityFramework 6.0.0-alpha3
EntityFramework 6.0.0-alpha2
EntityFramework 6.0.0-alpha1
EntityFramework 5.0.0
EntityFramework 5.0.0-rc
EntityFramework 5.0.0-beta2
EntityFramework 5.0.0-beta1
EntityFramework 4.3.1
EntityFramework 4.3.0
EntityFramework 4.3.0-beta1
EntityFramework 4.2.0
EntityFramework 4.1.10715
EntityFramework 4.1.10331
EntityFramework 4.1.10311

USE EntityFramework connection in ADO.Net Connection

Use Namespace

using System.Data.EntityClient

Use following code snippet to use Entity Framework connection in ADO.Net Connection

string _entityConnString = ConfigurationManager.ConnectionStrings["db_MyDatabase"].ConnectionString;
string _AdoConnString = new EntityConnectionStringBuilder(_entityConnString).ProviderConnectionString;

How to take Maximum Table ID in Database Table using Entity Framework

_context is object of Entity Framework dbContext

Entity Framework dbContext

Delclaring Object of dbContext

Model1 _context = new Model1();
Int32 _TopicIDFK = _context.myTable.Select(x => x.TableID).Max();

How to Delete Records from Database table using Enity Framework

var _item = (from c in _context.mytable.where(x=> x.id = 10) select c).FirstOrDefault();
_context.tblmyTable.Attach(_item);
_context.tblmyTable.Remove(_item);
_context.SaveChanges();

How to Save Records in Database Table using Entity Framework

var _obj = new tblmyTable();
_obj.TableID = 0;
_obj.Name = txtName.Text.Trim();
_context.tblmyTable.Add(_obj);
_context.Entry(_obj).State = EntityState.Added;
_context.SaveChanges();

How to Update/Modify Records in Database Table using Entity Framework

var _obj = new tblmyTable();
_obj = (from c in _context.tblmyTable.Where(x => x.ID == 10) select c).FirstOrDefault();
obj.ID = 10;
_obj.Name = txtName.Text.Trim();

_context.tblmyTable.Add(_obj);
_context.Entry(_obj).State = EntityState.Modified;
_context.SaveChanges();
How to Check Existing Records from database Table using Entity Framework
bool existsOption = _dtOptions.Select().ToList().Exists(row => row["Options"].ToString() == "technology");

How to run ADO.Net SQL Query using Entity Framework

string _sql = "select * from tblmyTable where ID in (" + _ids + ")";
var _img = _context.Set().SqlQuery(_sql);

How to take First Value from Database Table using Entity Framework

_IdQues = _context.tblmyTable.Select(u => u.QuestionIDPK).First();

How to count number of records using Entity Framework

_countQues = _context.tblmyTable.Where(u => u.ClassIDFK == _class && u.SubjectIDFK 
== _subject && u.ChapterIDFK == _chap && u.QuizIDFK == _quiz && u.isRecStatus == true).Count();

Rename Column Name in Database Table using Entity Framework

var query = (from c in _context.tblmyTable.Where(x => x.ID > 5).OrderBy(x => x.CountryIDPK)
 select new {Code = c.CountryIDPK, c.CountryName }).ToList();

Distinct: How to take Distinct Records from database Table using Entity Framework

var result = EFContext.TestAddresses.Select(m => m.Name).Distinct();

Pattern Matching: How to Pattern Matching using Entity Framework

var _results = from a in mycontainer.users where a.fullname.Contains(mySearchText) select a.fullname;

Join:How to join Multiple tables in Entity Framework 

I have provided here how to join multiple table using Entity Framework.

var query = (from A in _context.tblA
                 join B in _context.tblB on A.TablePk equals B.TableFK
                join C in _context.tblC on A.TablePK equals C.TableFK
                where (A.Amount >0) && (B.IsRecStatus == true) && (C.IsRecStatus == true)
                orderby A.Col1, B.Col1, C.Col1
                select new { A.tblPK, B.Col1, C.Col1, A.Col1, B.Col2, C.Col1 }).ToList();

Not In: Not In Statement using EntityFramework

Int32[] CityIDFK = {10,20,30}
var _lst = (from c in _context.tblDemo TableIDPK.Any(m => m == c.CityIDFK) select c).ToList();

Random Rows: How to pick random rows using Entity Framework

var _randomRows = (from c in _context.tblOrders.OrderBy(y => Guid.NewGuid()).Take(7) select c).ToList();

Summary

Entity Framework provides developer to code access capability so eliminate code lines. Now every .net framework (c#, Vb.net) developer is primary requirement to use Entity Framework in your application (MVC, ASP.Net, Windows applications). Major operations (CRUD) , count records, Maximum number in table, Column Rename etc of Entity Framework are explained above with coding snippet in great depth.

Suggested Reading

Friday

Entity Framework Error: Validation failed for one or more entities

Introduction

While running on C# app using Entity Framework, me came me the issue Validation failed for one or more entities. Today, I've prepared a whole solution to conquer issue Validation failed for one or more entities while playing with Entity Framework (MS SQL Server database) using C#, MVC, ASP.Net, VN.net etc. Recently, I've absolutely explained entity framework error Store update insert or delete statement affected an unexpected number of rows (0)  in my recent article, this day going to resolve another one entity framework error Validation failed for one or more entities.

Saturday

The underlying provider failed on open

Introduction

I am now providing resolution of The underlying provider failed on open problem whenever we are working on across the network. If you see inner exception message then you'll caught in below error description in detail. This problem comes often when we are working with Entity Framework with MVC, ASP.Net, Windows Forms, WPF etc. This issue can be encountered in noticed scenarios. I've also written in detail associated with issue  Could not open a connection to SQL Server.

Error Description

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. 
Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}