The Specified Store provider cannot be found in the configuration or is not valid - entity framework

Check your machine.config to see if any providers mentioned in the <DbProviderFactories> section. If not, add the required provider to this section.

This solved mine.

‘MyEntities’: type used in a using statement must be implicitly convertible to ‘System.IDisposable’

‘MyEntities’: type used in a using statement must be implicitly convertible to ‘System.IDisposable’

When trying to access the entity object which is in a different project, I added the reference of the project having the entity object to the new project and was trying to use the code block as below:

 using (Entities entities = new Entities(entityConnStr))
{

}

This gave me the error. The solution for this was to add a reference to 'System.Data.Entity' in the new project. After this it started working for me.

Creating Custom Properties for/extending the Entity framework generated class

How to create a custom property for an entity framework generated class. I had a requirement and the way I did was to create a partial class for the entity generated class. After creating the partial class, I added a property and was able to access the property in the same project.

BUT here comes problem.... This entity generated class was in a web service. After I deployed my web service and tried to test with a client application, I was not able to access the newly added custom property.


public partial class ClassA                       //original class, entity generated
    {
        public string Name { get; set; }
      
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String FROM_USER
        {
            get
            {
                return _FROM_USER;
            }
            set
            {
                OnFROM_USERChanging(value);
                ReportPropertyChanging("FROM_USER");
                _FROM_USER = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("FROM_USER");
                OnFROM_USERChanged();
            }
        }
        private global::System.String _FROM_USER;
        partial void OnFROM_USERChanging(global::System.String value);
        partial void OnFROM_USERChanged();
    }

public partial class ClassA                      //newly created partial class for the above class
    {
        public int ID { get; set; }
    }

 The above partial class didn't work for me. So I had to add an extra attribute for the new property as below:

public partial class ClassA
    {
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public int ID { get; set; }
    }

 This solution worked for me. Hope this tip helps you.

Operation is not valid due to the current state of the object - LinQ to Entities - table without a primary key

You get this error when trying to insert into a table without a primary key. If you open the edmx file in XML view and take a look into the EntitySet it will be having a DefiningQuery tag (see below).

<EntitySet Name="Table1" EntityType="Model2.Store.Table1" store:Type="Tables" store:Schema="TABDATA" store:Name="Table1">

<DefiningQuery>

 SELECT "Table1"."Column1" as "Column1",

"Table1"."Column2" as "Column2",

"Table1"."Column3" as "Column3",

"Table1"."Column4" as "Column4",

"Table1"."Column5" as "Column5",

"Table1"."Column6" as "Column6",

"Table1"."Column7" as "Column7"

from "TABDATA"."Table1" "Table1"

</DefiningQuery>
</EntitySet>

The solution is to remove the DefiningQuery tag for that EntitySet as below:

<EntitySet Name="Table1" EntityType="Model2.Store.Table1" store:Type="Tables" store:Schema="TABDATA" store:Name="Table1">

</EntitySet>

You can insert new records now without any error.

Deprecated Method for adding a new object to the [object] EntitySet. Consider using the .Add method of the associated ObjectSet property instead.

This is a message shown when using old method to Add objects to entity.

The old way (deprecated) of inserting a new record to database using Entity object was as below:
using (Entities entities = new Entities(entityConnStr))
{
   //....
  //.....
  entities.AddToMyTable1(myTableObject);
  entities.SaveChanges(); 
}

New way to Insert/Add records is as below:
using (Entities entities = new Entities(entityConnStr))
{
//....
//.....
entities.AddObject("MyTable1",myTableObject);
entities.SaveChanges();
}

How to write a left outer join in LINQ?

This can be accomplished by using DefaultIfEmpty(). See the example LINQ query below:

var provQuery = (from a in entities.Table1
                join c in entities.Table2 on a.ID equals c.ID into temppvcon
                from t2 in temppvcon.DefaultIfEmpty()
                where a.ID == Id
                select new { a.ID, a.NAME, t2.ID, t2.NAME });

Check out the SQL generated from the above LINQ query:

SELECT
1 AS "C1",
"Extent1"."ID" AS "ID",
"Extent1"."NAME" AS "NAME",
"Extent2"."ID" AS "ID",
"Extent2"."NAME" AS "NAME"
FROM  (SELECT
      "TABLE1"."ID" AS "ID",
      "TABLE1"."CON_ID" AS "CON_ID",
      "TABLE1"."NAME" AS "NAME",
      "TABLE1"."ADDED_DT" AS "ADDED_DT"
      FROM "DB"."TABLE1" "TABLE1") "Extent1"
LEFT OUTER JOIN (SELECT
      "TABEL2"."NAME" AS "NAME",
      "TABEL2"."PART" AS "PART",
      "TABEL2"."ID" AS "ID",
      "TABEL2"."CLUSTER_ID" AS "CLUSTER_ID"
      FROM "CDCDATA"."TABEL2" "TABEL2") "Extent2" ON "Extent1"."ID" = "Extent2"."ID"
WHERE ("Extent1"."ID" = :p__linq__0)

Hope this is clear. Enjoy...

How to view the SQL generated by LINQ to Entities?

There is a ToTraceString() method for objectQuery. The anonymous object returned from the LINQ query has to be casted into ObjectQuery as shown below to use the ToTraceString():

var provQuery = from a in entities.Table1 where a.ID == id orderby a.Name
                            select a;

The SQL generated from the LINQ can be viewed by using as below:

((System.Data.Objects.ObjectQuery)provQuery).ToTraceString()

You can copy from watch window and run the sql query to test it.