Wednesday, October 24, 2012

Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.

Issue:

While programming with Microsoft Entity Framework, you might have come across this error. The error occurs when calling the SaveChanges() method of the entity framework db context object. The original problem is not known unless you dig deeper into some of the property values of Entity Framework Exception classes.
Usually the exception raised is as below:

Server Error in '/' Application.

Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Entity.Validation.DbEntityValidationException: Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.

Solution:
You need to catch the exception (DbEntityValidationException ) and get into its properties to find the issue. Here is the catch block that will bring out the real issue:

C# Version:
catch (DbEntityValidationException dbEx)
{
    foreach (var validationErrors in dbEx.EntityValidationErrors)
    {
        foreach (var validationError in validationErrors.ValidationErrors)
        {
            Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
        }
    }
}

Wednesday, October 17, 2012

ATTACH DATABASE encountered operating system error 5 Access is denied Error

Issue:
While trying to attach an .MDF SQL Server Express Database to my current SQL Server Instance, I encountered with the below error. This was the error message received.

Attach database failed for Server 'HABEEB-HP\SQLEXPRESS'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Unable to open the physical file "C:\testApp\App_Data\MyDB.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476
------------------------------

Solution:
Googling quiet a bit mis led me to some version conflict reasons and file system security access issues. I tried to give full access to Network Service to the file. I tried to run SQL Server Service with Administrator Privilege from some suggestions online and nothing worked. Fiddling on it after a break I could figure out that its an SQL Server Management Studio (SSMS) privilege issue. I opened SSMS as Administrator and everything worked as a breeze.

Tuesday, October 9, 2012

When to use .First and .FirstOrDefault with LINQ?

Issue:
When to use .First() and when to use .FirstOrDefault() with LINQ?

This is a frequent question among .NET developers who use LINQ.
Many of the developers keep coding with this doubt being uncleared in the back of their mind.
This is a very simple and straight forward question and solution.

Solution:
Use .First() when you are pretty use that the LINQ query will definitely return at least one element in a sequence. In this case First() works pretty well and it will return the top 1 element from the resulting enumeration. But the sad part is when there is nothing returned executing the LINQ expression. In which case it will throw and exception. Practicing to catch the exception and always using only .First() is a very bad practice as it will affect the performance.

Use .FirstOrDefault() in the case where you cannot guarantee a result when executing a LINQ statement. In this case it gracefully returns the default value depending on the type. That is Null for reference types and the default values for primitive types. For example the default value for int is 0;

Transpose Columns into Rows (UNPIVOT)

Issue:
How to Transpose (PIVOT (actually UNPIVOT ) / Transform) Columns into Rows
Sometimes you want to transpose Columns into Rows in SQL Server.

Solution:
The below T-SQL will transpose or transform Columns into Rows. It uses the reverse of PIVOT which is UNPIVOT.

DECLARE @Table Table
(NameCol1 varchar(10),
NameCol2 varchar(10),
NameCol3 varchar(10))
INSERT INTO @TABLE VALUES ('Name 1', 'Name 2', 'Name 3')
--INSERT INTO @TABLE VALUES ('Name 4', 'Name 5', 'Name 6')
--INSERT INTO @TABLE VALUES ('Name 7', 'Name 8', 'Name 9')

SELECT Name, Nameval
FROM
(SELECT NameCol1, NameCol2, NameCol3
FROM @TABLE) p
UNPIVOT
(NameVal FOR Name IN
(NameCol1, NameCol2, NameCol3)
)AS unpvt


-- OUTPUT
Name             Nameval
-------------     ----------
NameCol1      Name 1
NameCol2      Name 2
NameCol3      Name 3