Getting error on add new “Table ‘conjero.tmpidentity_project_consultant_order’ doesn’t exist” on MySql CodeFirst with EF

In my project I am handling two type auto generated keys. Long and Guid. Previously long only i used in this project. Now added Guid also. I previously used this with out any with out any problem in MSSQL. I created one table via migration. It works fine. In the case of second table, I created model and table separately for a reason.  At this time I getting an error while am try to add something.

Table ‘conjero.tmpidentity_project_consultant_order’ doesn’t exist

I don’t have any idea about tmpidentity_. What it is ?

Search in google about tmpidentity and got one link, https://github.com/pdonald/mysql-connector-net/blob/master/Source/MySql.Data.Entity/MySqlMigrationSqlGenerator.cs

Here talking about some TRIGGER. So I checked previously created table and its trigger. there is trigger for handling auto generated guid. I Just copy paste that trigger and rename as per second table name.

My issue fixed!!

Following is the trigger:

CREATE DEFINER=`root`@`%` TRIGGER `conjero`.`project_consultant_order_BEFORE_INSERT` BEFORE INSERT ON `project_consultant_order` FOR EACH ROW
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmpIdentity_project_consultant_order;
		CREATE TEMPORARY TABLE tmpIdentity_project_consultant_order (guid CHAR(36))ENGINE=MEMORY;
		SET @var_Id = UUID();
		INSERT INTO tmpIdentity_project_consultant_order VALUES(@var_Id);
		SET new.Id = @var_Id;
END

Entity Framework – AutoDetectChangesEnabled

From MSDN

When using most POCO entities the determination of how an entity has changed (and therefore which updates need to be sent to the database) is handled by the Detect Changes algorithm. Detect Changes works by detecting the differences between the current property values of the entity and the original property values that are stored in a snapshot when the entity was queried or attached. The techniques shown in this topic apply equally to models created with Code First and the EF Designer.

But some times we need to turn off this feature. for example, I need to upload a excel file it contains a bulk data. At this time AutoDetectChangesEnabled is true then it take a lot time for insert all rows. I can do the data validations at db side.

Just check the difference of AutoDetectChangesEnabled ON and OFF while inserting a bulk data.

Normal way:

namespace TestAutoDetectChangesEnabled
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Title = "Time Test - AutoDetectChangesEnabled";
            string[] names = { "Arun", "Raj", "Musthaan", "Majeed", "Muzafir",
                                 "Hari", "Dharma", "Kannan", "Imran", "Santo",
                                 "Rahmath", "Jayesh" };
            Stopwatch sw = new Stopwatch();
            sw.Start();
            var db = new AppContext();
            foreach (var i in Enumerable.Range(1, 20000))
            {
                var student = new Student()
                {
                    Name = names[(new Random().Next(0, 10))],
                    ContactNo = "9870002735",
                    RegNo = i.ToString().PadLeft(7, '0')
                };
                db.Students.Add(student);
                Console.Clear();
                Console.WriteLine("{0} records added", i);
            }
            db.SaveChanges();
            sw.Stop();
            Console.WriteLine("Taked time - {0}", sw.ElapsedMilliseconds);
            Console.ReadKey();
        }
    }

    public class AppContext : DbContext
    {
        public AppContext()
            : base("AppConnection")
        {

        }
        public DbSet<Student> Students { get; set; }
    }
    public class Student
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string RegNo { get; set; }
        public string ContactNo { get; set; }

    }
}

AutoDetectChangesEnabled-before

After disable AutoDetectChanges

 class Program
    {
        static void Main(string[] args)
        {
            Console.Title = "Time Test - AutoDetectChangesEnabled";
            string[] names = { "Arun", "Raj", "Musthaan", "Majeed", "Muzafir",
                                 "Hari", "Dharma", "Kannan", "Imran", "Santo",
                                 "Rahmath", "Jayesh" };
            Stopwatch sw = new Stopwatch();
            sw.Start();
            var db = new AppContext();
            try
            {
                db.Configuration.AutoDetectChangesEnabled = false;

                foreach (var i in Enumerable.Range(1, 20000))
                {
                    var student = new Student()
                    {
                        Name = names[(new Random().Next(0, 10))],
                        ContactNo = "9870002735",
                        RegNo = i.ToString().PadLeft(7, '0')
                    };
                    db.Students.Add(student);
                    Console.Clear();
                    Console.WriteLine("{0} records added", i);
                }
                db.SaveChanges();
            }
            finally
            {
                db.Configuration.AutoDetectChangesEnabled = true;
            }
            sw.Stop();
            Console.WriteLine("Taked time - {0}", sw.ElapsedMilliseconds);
            Console.ReadKey();
        }
    }

AutoDetectChangesEnabled-after

See the difference, it almost 10x faster. But use it with care. Don’t forget to re-enable detection of changes after the loop — used a try/finally to ensure it is always re-enabled even if code in the loop throws an exception.

See System.Data.Entity.Validation.DbEntityValidationException Details

Today i got an error on saving data to db using EF.

An exception of type ‘System.Data.Entity.Validation.DbEntityValidationException’ occurred in EntityFramework.dll but was not handled in user code

Additional information: Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details.

System.Data.Entity.Validation.DbEntityValidationException

but I can’t find out what are the failed validations.

System.Data.Entity.Validation.DbEntityValidationException2

No more details in quick watch. I stucked a little. At this time i find out the use of $exception.That is a special debugger variable. You cannot access it via code.

https://msdn.microsoft.com/en-us/library/ms164891.aspx

https://blogs.msdn.microsoft.com/shaykatc/2004/02/20/vs-2003-tip-3-view-exception-information-with-exception/

look  $exception in quickwatch.

System.Data.Entity.Validation.DbEntityValidationException3

 

User.Identity.Name is null

At this time I am set claims like following line.

identity.AddClaim(new Claim("UserName", context.UserName));

I changed this to


using (AuthRepository _repo = new AuthRepository())
{
  IdentityUser user = await _repo.FindUser(context.UserName, context.Password);

  if (user == null)
  {
     context.SetError("invalid_grant", "The user name or password is incorrect.");
     return;
  }
  ClaimsIdentity identity = new ClaimsIdentity(OAuthDefaults.AuthenticationType);
  identity.AddClaim(new Claim(ClaimTypes.Name, user.UserName));
  identity.AddClaim(new Claim(ClaimTypes.NameIdentifier, user.Id));
  context.Validated(identity);

}

IAppBuilder does not contain definition for UseWebApi

When I try to implement WebApi with owin, i got an error

IAppBuilder’ does not contain a definition for ‘UseWebApi’ and no extension method ‘UseWebApi’ accepting a first argument of type ‘IAppBuilder’ could be found (are you missing a using directive or an assembly reference?)

Solution is you need to install Microsoft ASP.NET Web API2 OWIN Self Host which will resolve the issue.

You can use the NuGet command Install-Package Microsoft.AspNet.WebApi.OwinSelfHost.
nuget_webapi_selfhost

The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role

web_services[1]Some days before I got an error while try to update migration like :

UploadedData_GrpCategory_Target_UploadedData_GrpCategory_Source: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property ‘Category’ on entity ‘UploadedData’ does not match the type of property ‘Id’ on entity ‘GrpCategory’ in the referential constraint ‘UploadedData_GrpCategory’.

 

 

I didn’t understand what it is in first look.  Then I understood , I created Primary key and foreign key in different datatypes in Entities.

DataTable to Dynamic List

format strings

Last day I got a requirement, it need a pivot table. I generated this table in SQL and read it to a datatable. No issues. Then I want convert this datatable to a list. I can’t create a entity because columns are dynamic. I stuck here some time and got a solution .

 

 

public static class HelperExtensions
{
  public static List<dynamic> ToDynamic(this DataTable dt)
  {
    var dynamicDt = new List<dynamic>();
    foreach (DataRow row in dt.Rows)
    {
      dynamic dyn = new ExpandoObject();
      dynamicDt.Add(dyn);
      foreach (DataColumn column in dt.Columns)
      {
         var dic = (IDictionary<string, object>)dyn;
         dic[column.ColumnName] = row[column];
      }
    }
    return dynamicDt;
  }
}