Monday, July 29, 2013

DbEntityValidationException - Entity framework validation error handling

A sample code to catch part of an error handling when dealing with Entity Framework.
try { ... } catch (DbEntityValidationException dbEx) { #region LINQ query to get EF DB entity validation errors /* var errMsgs = dbEx.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage).ToArray(); var exceptionFullErrMsg = string.Concat( dbEx.Message, " Validation errors (DbEntityValidationException): " + UtilityHelper.ErrMsgBuilder(errMsgs)); log.Error(exceptionFullErrMsg); */ #endregion #region Loop to find Entity Framework DB entity validation errors foreach (var validationErrors in dbEx.EntityValidationErrors) foreach (var validationError in validationErrors.ValidationErrors) log.Error(UtilityHelper.ErrMsgBuilder(new string[] { "DbEntityValidationException" , validationError.ErrorMessage } )); #endregion } catch (Exception ex) { #region Exception logging log.Error(UtilityHelper.ErrMsgBuilder( new string[] { ex.GetType().Name } ), ex); #endregion } finally { ... } ... #region Utility Helper class public class UtilityHelper { public static string ErrMsgBuilder(String[] msgArray) { StringBuilder sbErr = new StringBuilder(); foreach (string msg in msgArray) sbErr.AppendLine(msg); return sbErr.ToString(); } } #endregion

Share/Bookmark

Friday, July 26, 2013

Unable to update the EntitySet 'EntityName' because it has a DefiningQuery and no element exists in the element to support the current operation.

Tied to debug my unit [TestMethod] in a unit test project through Test Explorer in Visual Studio 2012, then it tried to save the entity changes in database through a DbContext and it throw the folowing error message: 'Unable to update the EntitySet 'EntityName' because it has a DefiningQuery and no element exists in the element to support the current operation.'

In my case the problem was incorrect Entity Key setting on my entity in EF Model plus forgetting to set primary key in DB table. After setting primary key for the table which entity pointing too, I needed to update the model and open the EF model and manualy set Entity Key for primary key related member in the entity and clearing Entity Key setting for unnecessary entity members. Then rebuild, it worked. The reason was EF created a compound key made of all non nullable entity members which I had to reset them all.

It was my case but there are other possibilities for this error which already discussed if you search on Internet (Google or bing it)
Share/Bookmark

Wednesday, July 17, 2013

SQL - Record count for all tables in a DB

I needed to count the number of records after a DB backup and restore then the following script helped to do that.
USE [Database_Name] GO CREATE TABLE #tblRecCount ( tblName varchar(255), tblRowCount int ) EXEC sp_MSForEachTable @command1='INSERT #tblRecCount (tblName, tblRowCount) SELECT ''?'', COUNT(*) FROM ?' SELECT tblName, tblRowCount FROM #tblRecCount ORDER BY tblName, tblRowCount DESC SELECT SUM(tblRowCount) AS totalDbRowCount FROM #tblRecCount DROP Table #tblRecCount
The original post is available here.
Share/Bookmark

Tuesday, July 16, 2013

How to add a digital ID to adobe reader (.pfx exported certificate file)

When you see a PDF document after opening with a message that it’s signed but not valid, you need to import the necessary .pfx certificate file, if you have the .pfx file simply add it as following: Adobe reader :: Edit –> Protection –> Security Settings -> Windows Digital IDs then now click on “Add ID” and follow the wizard to impost the certificate.
Share/Bookmark

Sunday, April 14, 2013

Microsoft Security Essentials - a must have security pieces to protect your system

Get Microsoft Security Essentials for the low, low price of free. http://windows.microsoft.com/en-US/windows/security-essentials-download

Saved me from Sirefef.gen!C which is a computer virus that intercepts secure web connections and can steal passwords and other sensitive data. Chrome recognises this virus, but it affects all software on the computer. Other browsers and software may continue to work but they are also affected and rendered insecure.

Microsoft Security Essentials can reportedly remove this virus. When the virus is removed, the warnings in Chrome will stop.
Share/Bookmark

Thursday, November 1, 2012

SQL - EDMPASS Nov. 20th, 2012

Key Performance Indicators that PerformancePoint can obtain from the SQL BI Data Wharehouse using a Dashboard http://lnkd.in/igQM4m
Share/Bookmark

Thursday, October 25, 2012

TSQL - Partial Duplicate Indexes

Paul Nielsen has another T-SQL code which helps to find partial duplicate indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) which would be considered duplicate indexes. This query only examines key columns and does not consider included columns.
WITH indexcols AS ( SELECT object_id AS id , index_id AS indid , name , (SELECT CASE keyno WHEN 0 THEN NULL ELSE colid END AS [data()] FROM sys.sysindexkeys AS k WHERE k.id = i.object_id AND k.indid = i.index_id ORDER BY keyno, colid FOR XML PATH('') ) AS cols FROM sys.indexes AS i ) SELECT object_schema_name(c1.id) + '.' + object_name(c1.id) AS 'Table Name' , c1.name AS 'Index' , c2.name AS 'Partial Duplicate' FROM indexcols AS c1 JOIN indexcols AS c2 ON c1.id = c2.id AND c1.indid < c2.indid AND c1.cols <> c2.cols -- remove exact duplicates AND ((c1.cols like c2.cols + '%' AND SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ') OR (c2.cols like c1.cols + '%' AND SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' ')) ORDER BY [Table Name]

Share/Bookmark

Wednesday, October 24, 2012

TSQL - Exact Duplicate Indexes

Paul Nielsen provided T-SQL code which helps to find exact duplicate indexes. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints.
with indexcols as ( select object_id as id , index_id as indid , name , (select case keyno when 0 then NULL else colid end as [data()] from sys.sysindexkeys as k where k.id = i.object_id and k.indid = i.index_id order by keyno, colid for xml path('')) as cols , (select case keyno when 0 then colid else NULL end as [data()] from sys.sysindexkeys as k where k.id = i.object_id and k.indid = i.index_id order by colid for xml path('')) as inc from sys.indexes as i ) select object_schema_name(c1.id) + '.' + object_name(c1.id) as 'Table Name' , c1.name as 'index' , c2.name as 'exactduplicate' from indexcols as c1 join indexcols as c2 on c1.id = c2.id and c1.indid < c2.indid and c1.cols = c2.cols and c1.inc = c2.inc ORDER BY [Table Name]

Share/Bookmark

Saturday, October 15, 2011

Database table indexes with type and size

The following query retrieves index names and shows type and size of indices.

SELECT
sysSchma.name AS SchemaName
, sysObj.name AS TableName
, sysIndx.name AS IndexName
, sysIndx.type_desc AS IndexType
, sysPartStat.used_page_count * 8 AS IndexSizeKB
, CAST(sysPartStat.used_page_count * 8 / 1024.00 AS Decimal(10,3))
AS IndexSizeMB
FROM sys.dm_db_partition_stats AS sysPartStat
INNER JOIN sys.indexes AS sysIndx
ON sysPartStat.[object_id] = sysIndx.[object_id]
AND sysPartStat.index_id = sysIndx.index_id
AND sysIndx.type_desc <> 'HEAP'
INNER JOIN sys.objects AS sysObj
ON sysObj.[object_id] = sysPartStat.[object_id]
INNER JOIN sys.schemas AS sysSchma
ON sysObj.[schema_id] = sysSchma.[schema_id]
AND sysSchma.name <> 'SYS'
-- WHERE partition_stats.[object_id] = object_id('dbo.TableName')
ORDER BY SchemaName, TableName, IndexName, IndexType

Share/Bookmark

Monday, September 5, 2011

Dumpbin.exe - a tool to provide info about DLLs (x86 or x64)

Dumpbin.exe is one of tools with ability to provide information about the format and symbols in executable, library, and DLL files.

There are some options which you can utilize with Dumpbin.exe utility like /ALL, /DISASEM, /SUMMARY, /SYMBOLS, ...

If you want to see a DLL is compiled in x86 or 64 bit you can use /HEADERS option in command line:

dumpbin /headers MyCompany.MyDepartment.MyNameSapece.MyAssembly.dll

To be able to call DumpBin.exe in command line, you better extend PATH to be able to call it from anywhere: (The path is depend on your system)
  • SET PATH=%PATH%;C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\bin;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE;
or
  • SET PATH=%PATH%;C:\Program Files\Microsoft Visual Studio 10.0\VC\bin;C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE;

Share/Bookmark