.Net / Error “log4net:ERROR [AdoNetAppender] Failed in DoAppend System.TypeInitializationException: The type initializer for ‘log4net.GlobalContext’ threw an exception”

Background

Ran into a nice easy to fix error.

Error

Here is the error:

Image

Text


-- More  -- log4net:ERROR [AdoNetAppender] Failed in DoAppend
System.TypeInitializationException: The type initializer for 'log4net.GlobalContext' threw an exception. ---> System.Con
figuration.ConfigurationErrorsException: Configuration system failed to initialize ---> System.Configuration.Configurati
onErrorsException: Only one <configSections> element allowed per config file and if present must be the first child of the root <configuration> element. 
(hello.exe.Config line 11)
   at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
   at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors)
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
   --- End of inner exception stack trace ---
   at System.Configuration.ConfigurationManager.PrepareConfigSystem()
   at System.Configuration.ConfigurationManager.GetSection(String sectionName)
   at System.Configuration.PrivilegedConfigurationManager.GetSection(String sectionName)
   at System.Net.Configuration.SettingsSectionInternal.get_Section()
   at System.Net.Sockets.Socket.InitializeSockets()
   at System.Net.Dns.GetHostName()
   at log4net.Util.SystemInfo.get_HostName()
   at log4net.GlobalContext..cctor()
   --- End of inner exception stack trace ---
   at log4net.GlobalContext.get_Properties()
   at log4net.Core.LoggingEvent.CreateCompositeProperties()
   at log4net.Core.LoggingEvent.CacheProperties()
   at log4net.Core.LoggingEvent.FixVolatileData(FixFlags flags)
   at log4net.Appender.BufferingAppenderSkeleton.Append(LoggingEvent loggingEvent)
   at log4net.Appender.AppenderSkeleton.DoAppend(LoggingEvent loggingEvent)



 

TroubleShooting / Remediation

Error Message

The key data from our error message is highlighted below:

System.TypeInitializationException: The type initializer for ‘log4net.GlobalContext’ threw an exception. —>
System.Configuration.ConfigurationErrorsException: Configuration system failed to initialize —> System.Configuration.ConfigurationErrorsException:
Only one <configSections> element allowed per config file and if present must be the first child of the root <configuration> element.
(hello.exe.Config line 11)

 

Configuration File

[app].config

Original


<?xml version="1.0" encoding="utf-8" ?>
<configuration>

    <appSettings>
        <add key="fileDebug" value="debug" />
    </appSettings>
  
    <configSections>
        

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
    </configSections>
    
    <log4net debug="false">
       
        <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender" >
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%message%newline" />
            </layout>
        </appender>

        ....
        ....

Revision


<?xml version="1.0" encoding="utf-8" ?>
<configuration>

    <!--
        <appSettings>
            <add key="fileDebug" value="debug" />
        </appSettings>
    -->  
    
    <configSections>
        
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
    </configSections>
    
    <log4net debug="false">
    

        <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender" >
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%message%newline" />
            </layout>
        </appender>
               
         
    </log4net>

    <!-- moved here on 2018-02-13 by dadeniji --> 
    <appSettings>
        <add key="fileDebug" value="debug" />
    </appSettings>
    
</configuration>

 

Summary

Again, the fix is to make sure that nothing comes before the configSections element/section in our App’s configuration file ( *.exe.config).

Log4Net just happens to be the scapegoat here…

.Net – Raise the Debugger in the event of an error

Background

Many years ago a good friend showed my a programming trick he learnt from a Vendor.

The trick goes like this:

If a file is present, launch the debugger.

To offer an incline on how long people have been using this trick, I saw it on a computer running IBM OS/2.  And, the programming language was C.

Personally, there is a lot of things that I miss from server side, service level, programming.

It is good to see it is still workable in .Net

Sample Code

Source Code

debugMode.cs


using System;
using System.Text;
using System.IO;
using System.Configuration;
using System.Diagnostics;
  
class debugMode
{

  static string strFileDebug;
  static string PARAMETER_FILE_DEBUG = "fileDebug";      
  
  static Boolean bFileExists = false;
  
  static string strMessage;
  
  static void Main(string[] args)
  {

	debugMode objDebugMode;       
	
	   objDebugMode = new debugMode();
	   
			objDebugMode.parse();
		
			objDebugMode.processDebug();
		
	objDebugMode = null;
   
  }


  
  public debugMode()
  {
			 
  }

  
  
  public void parse()
  {
	  
	   getConfigurationData();
	   
	   bFileExists = File.Exists(strFileDebug);
	  
  }
	
  private void getConfigurationData()
  {
			  
	try
	{

		strFileDebug = ConfigurationManager.AppSettings[PARAMETER_FILE_DEBUG];
		
	   
	}
	catch(Exception objEx)   
	{
		
	   strMessage = "Exception occurred " + objEx.Message;
					
	   Console.WriteLine(strMessage);
	   
	   
	}                  
	 
  }
  
  
  public void processDebug()
  {
	  
	  if (bFileExists)
	  {
			// Start debugger
			System.Diagnostics.Debugger.Break();
			
	  }
	  
  }
  
		
}



makeDebugDemo.cmd


set "_compiler=C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\"

set "_FILE_SET= debugMode.cs "

set "_OPTION=/nologo /debug "

set "_ADDLIB_S= /r:System.dll"
set "_ADDLIB_SD= /r:System.Data.dll"
set "_ADDLIB_IO= /r:System.IO.dll"
set "_ADDLIB_WB= /r:C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\WPF\WindowsBase.dll"

set "_MAINCLASS=debugMode"

set "_APPNAME=debugDemo.exe"

set "_MAIN=/main:%_MAINCLASS%"

if exist %_APPNAME% del %_APPNAME%

%_compiler%\csc.exe /out:%_APPNAME%  %_OPTION% %_ADDLIB_L4N% %_ADDLIB_S% %_ADDLIB_SD% %_ADDLIB_IO% %_ADDLIB_WB% %_MAIN%  %_FILE_SET%

 

Configuration

debugDemo.exe.config

In the app.config file, please set the file that will trigger the debugger by setting fileDebug value.


<?xml version="1.0" encoding="utf-8" ?>
<configuration>

	<appSettings>
		<add key="fileDebug" value="debug" />
	</appSettings>
	
</configuration>	


Lab

Let us test things out:

  1. create marker file ( debug )
  2. Compile App
  3. Run app

 

Compile App

Command

makeDebugDemo.cmd

Output

Run App


debugDemo.exe

 

Run App

Image

…has stop working

 Visual Studio Just-In-Time Debugger

New Instance of Visual Studio 2010

New Instance of Visual Studio Community 2017

 

Microsoft Visual Studio ( Debugging )

.Net – LINQ – Exception – “Sequence contains no elements”

Background

Playing around a bit with LINQ / Entity Framework and running into exceptions.

Unfortunately the code itself is Vendor Supplied and reasonable big and so I am having to set up little test code snippets to have a playground.

With a playground we can try to see if we will be able to set up the right conditions that will “force” the error.

 

NoDB

To make things easy and self contained, we will not have an actual database.

We’ll just declare a simple class and create a few instance of the class.

Each instantiation will be added into a list.

Data

Athlete Team Position
Svetlana Omelchenko 98
Claire O’Donnell 75
Sven Mortensen 88
Cesar Garcia 65

 

Code

Class – Student



    public class Student  
    {  
	
        public string FirstName { get; set; }  
        public string LastName { get; set; }  
        public int Score { get; set; }  

		
    }  


 

Populate List


static ArrayList arrList = new ArrayList();  

static void populateList()
{
	
				
	arrList.Add
	(  
		new Student  
			{  
				  FirstName = "Svetlana"
				, LastName = "Omelchenko"
				, Score = 98  
			}
	);  
	
	arrList.Add
	(  
		new Student  
			{  
				  FirstName = "Claire"
				, LastName = "O’Donnell"
				, Score = 75
			}
	);  
	
	arrList.Add
	(  
		new Student  
			{  
				  FirstName = "Sven"
				, LastName = "Mortensen"
				, Score = 88
			}
	);  
	
	arrList.Add
	(  
		new Student  
			{  
				  FirstName = "Cesar"
				, LastName = "Garcia"
				, Score = 65
			}
	);  
	
}

 

Get Minimum Score to display


Console.Write("Please enter minimum score :- ");

String value = Console.ReadLine();

int iScore = -1;


try 
{
	
	iScore  = Int32.Parse(value); 
		
	Console.WriteLine("{0} --> {1}", value, iScore);
}
catch (FormatException) 
{
	Console.WriteLine("{0}: Bad Format", value);
}   

catch (OverflowException) 
{
	Console.WriteLine("{0}: Overflow", value);   

}  

 

Query List (arrList) filtering on mininal score


var query = from Student student in arrList  
	    orderby student.Score descending
            where student.Score > iScore
            select student
						;  

Simple Result Set Iteration


						
foreach (Student s in query) 
{			
   Console.WriteLine(s.LastName + ": " + s.Score);  
};  

Access Recordset


/*
	Get First Element in recordset
*/
try
{

	//get First element in set
	var varElement = query.First();

	if (varElement != null)
	{
		Console.Write("Lastname is :- " );
		Console.WriteLine(varElement.LastName);
	}

}
catch (Exception ex)
{

	Console.WriteLine("Error getting first element - query.First()");

	Console.WriteLine("Exception Message:- " + ex.Message);

	Console.WriteLine("StackTrace:- " + ex.StackTrace);

	Console.WriteLine(STRING_LINEBREAK);
}


/*
	Get Average Score in recordset
*/
try
{

	fltAverage = query.Average(e => e.Score);

	Console.Write("Average Score " );
	Console.WriteLine(fltAverage);


}
catch (Exception ex)
{

	Console.WriteLine("Error getting average score - query.Average(e => e.Score) ");

	Console.WriteLine("Exception Message:- " + ex.Message);

	Console.WriteLine("StackTrace:- " + ex.StackTrace);
				
	Console.WriteLine(STRING_LINEBREAK);

}					


Exception

  1. Exception Message:-
    • Sequence contains no elements
  2. Exception StackTrace :-
    • at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
    • at System.Linq.Enumerable.Average(IEnumerable`1 source)

Remediation

Plan

Our hope is that we will need a couple of helper methods.
The helper methods will check the recordset of a LINQ Query and compute counts, isEmpty, etc.

Extension Methods

Lasse Espeholt

Code provided by Lasse Espeholt here.

 

static class EnumerableExtensions
{
	public static int Count(this IEnumerable source)
	{
		int res = 0;

		foreach (var item in source)
		{
			res++;
		}
		
		return res;
	}
	
	public static Boolean isEmpty(this IEnumerable source)
	{
		Boolean bEmpty = true;

		foreach (var item in source)
		{
			bEmpty = false;
			break;
		}
		
		return (bEmpty);
	}
	
}

Validate data before Use

 

// Get number of Elements
iNumberofElements = query.Count();

//Is List Empty
bEmpty = query.isEmpty();

// If List is not empty	
if (bEmpty == false)
{
	
	Console.WriteLine("Count :- " + iNumberofElements);	

	//get First element of a Sequence
	var varElement = query.FirstOrDefault();

	//If first element is not null
	if (varElement != null)
	{
	
		Console.WriteLine("Lastname :- " + varElement.LastName);
	
	}	
	
	fltAverage = query.Average(e => e.Score);
	
	Console.WriteLine("Average Score :- " + fltAverage);
	
}	
else
{
	
	Console.Write("Empty recordset returned from searching for test scores greater than :- ");
	
	Console.WriteLine(iScore);
}		
	

 

Summary

There are a few safeguards that one can employ.  Example are listed below:

  1. Sequence.FirstOrDefault
  2. If Sequence.Any the Sequence.First
  3. Sanitize recordset using count or exists before aggregating data

 

References

  1. docs.microsoft.com
    •  Docs/NET / C# Guide / Programming guide / Programming concepts / LINQ
      • How to: Query an ArrayList with LINQ (C#)
        Link
  2. Data Developer Center
    • Performance Considerations for EF 4, 5, and 6
      Link
  3. Technical Overhead
    • Nathan
      • LINQ Single vs SingleOrDefault vs First vs FirstOrDefault
        Link
  4. DotNetPerls
    • FirstOrDefault
      Link
  5. Telerik
    • How to: Convert the Results of a LINQ Query to an Array
      Link
  6. Tech Crazy Zone
    • Gihan Lakmal
      • C# – Fix Sequence Contains no elements
        Link
  7. Stack Overflow
    • Calculating Count for IEnumerable (Non Generic)
      Link
    • Implicitly typed local variables must be initialized
      Link
    • Entity Framework 4 Single() vs First() vs FirstOrDefault()
      Link
    • Sequence contains no elements?
      Link

Entity Framework – Change Database Column – Error – “could not be set to a ‘System.Boolean’ value. You must set this property to a non-null value of type ‘System.String'”

 

Background

Upon changing one of our database columns from varchar to bit, we experienced an invalid “System.InvalidOperationException” issue.

 

Error

Error – “Unhandled Exception: System.InvalidOperationException: The ‘status’ property on ‘usp_TraceFlagSourceFetch_Result1’ could not be set to a ‘System.Boolean’ value.
You must set this property to a non-null value of type ‘System.String'”

Here is the error we are experiencing.

Error Text


Unhandled Exception: System.InvalidOperationException: The 'status' property on 'usp_TraceFlagSourceFetch_Result1' could not be set to a 'System.Boolean' value.
You must set this property to a non-null value of type 'System.String'.
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
at lambda_method(Closure , Shaper )
at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at lab001VS.UITraceFlag.listTraceFlagSourceUseSPImplicit(Int32 iID) in

TroubleShooting

We need to find “usp_TraceFlagSourceFetch_Result1

 

Model Browser

We found usp_TraceFlagSourceFetch_Result1 via the Model Browser.

Model Browser – Original

We looked in the Model Browser and this is what it looks like:

 

Model Browser – Original – Column – Status

Let us review the identified Property, Status.

 

Model Browser – Original – Column – Status – Type – “String”

Model Browser – Original – Column – Status – Type – “Boolean”

Please change the Type of Status from String to Boolean.

 

 

Summary

Really impressed with how quickly Entity Framework makes it to align .Net code with database structural changes.

This expediency especially when change can be localized to the Model and made via the Model Browser is intriguing.

 

 

Entity Framework – Using Database Functions

Background

We are going to add functionality to add records to our referenced table.

The table that we will be adding data has a primary key consisting of two columns.

The two columns are id and sequenceNbr.

The id column binds us to the parent\referenced table.

And, the sequenceNbr is a progressive column that we increment for each new record belonging to the same group.

 

Why Scaler Function?

The Scaler function will be employed on the Client.

It will connect to the backend and retrieve the last used Sequence Number for the group.

 

Utilize – Scaler Function – Get Current In-use SequenceNbr

SQL Server – Management Studio ( SSMS )

Database – Diagram

 

Function

dbo.udfn_TraceFlagSource_GetSequenceNbrMax
Outline
  1. Query [dbo].[traceFlagSource]
    • Get Max(sequenceNbr)
      • Filter
        • Filter on [id] = traceID

Code

if object_id('[dbo].[udfn_TraceFlagSource_GetSequenceNbrMax]') is null
begin

	exec('create function [dbo].[udfn_TraceFlagSource_GetSequenceNbrMax]
		   ()
		   returns int
		   as
		   begin
				return 1/0
		   end
			')

end
go

alter function [dbo].[udfn_TraceFlagSource_GetSequenceNbrMax]
(
	@traceID int
)
returns int
as
begin

	return
	(
		ISNULL
		(
			(
				select max
						(
							tblTFS.[sequenceNbr]
						)

				from   [dbo].[traceFlagSource] tblTFS

				where  tblTFS.[id] = @traceID

			)
			, 0
		)
	)


end
go


 

Visual Studio

Outline

Here are the steps:

  1. Launch Visual Studio
  2. Update Data Model

 

Data Model

 

Model Browser

Model Browser – Before Import Database Function

Here is the Model Browser before importing the Database Function

 

Model Browser – After Importing Database Function – dbo.udfn_TraceFlagSource_GetSequenceNbrMax

Here is the screen once we import the  dbo.udfn_TraceFlagSource_GetSequenceNbrMax  scalar function.

 

Add Function Import

Tried to Add Function Import…

But, none of the functions that we have defined in the Database shows up.

 

Utilize – Table Function – Get Current In-use SequenceNbr

SQL Server – Management Studio ( SSMS )

 

Function

dbo.TVF_TraceFlagSource_GetSequenceNbrMax
Outline
  1. Query [dbo].[traceFlagSource]
    • Get Max(sequenceNbr)
      • Filter
        • Filter on [id] = traceID
Code

if object_id('[dbo].[TVF_TraceFlagSource_GetSequenceNbrMax]') is null
begin

	exec('create function [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax]
		   ()
		   returns TABLE
		   as
		   return
		   (

				select
					[shell] = 1/0
		   )
			')

end
go

ALTER FUNCTION [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax]
(
	@traceID int
)
RETURNS TABLE 
AS
RETURN 
(
	select
		[sequenceNbr]
			= ISNULL
			(
				(
					select max([sequenceNbr])
					from   [dbo].[traceFlagSource] tblTFS
					where  tblTFS.[id] = @traceID
				)
				, 0
			)

)
GO

grant select on [dbo].[TVF_TraceFlagSource_GetSequenceNbrMax] to [edmgen]
go


Visual Studio

Outline

Here are the steps:

  1. Launch Visual Studio
  2. Update Data Model

 

Data Model

Model Browser – After Importing Database Function – Table Value Function – dbo.TVF_TraceFlagSource_GetSequenceNbrMax

Here is the screen once we import the  dbo.udfn_TraceFlagSource_GetSequenceNbrMax  scalar function.

 

Add Function Import

Tried to Add Function Import…

Still Functions, Scaler or Table Functions, show up.

Model Browser

Thankfully, our Table Value Function is automatically created.

It is shown under “Function Imports” \ “TVF_TraceFlagSource_GetSequenceNbrMax“.

 

Code

Outline

  1. Methods
    • traceFlagSourceAdd
      • Display prompt asking user to enter data
      • Accept data
      • Start Transaction
        • Serializable
          • Ensure that getting current Max Sequence Number and usage of the data are handled in a single transaction
      • Call Entity Framework asking for Max Sequence Number
        • Pass along variables
          • Trace ID
        • Process Result Set
          • Get First Record
        • Increment Max Value as we prepare for Insert
      • Instantiate traceFlagSource
      • Populate traceFlagSource
      • Save Changes
        • If error, invoke  handleDBException method
    • handleDBException
      • Depending on the type of Exception access different Exception Type
    • getDBExceptionMessage
      • Argument Type :- System.Data.Entity.Validation.DbEntityValidationException
        • Access EntityValidationErrors
        • Access ValidationErrors
      • Argument Type :- System.Data.Entity.Infrastructure.DbUpdateException
        • System.Data.SqlClient.SqlException = ex.InnerException
        • Iterate sqlException.Errors, accessing each System.Data.SqlClient.SqlError in the collection

 

traceFlagSourceAdd


public void traceFlagSourceAdd()
{

	Console.WriteLine("Trace Flag ID");
	strID = Console.ReadLine();

	if (strID == "")
	{
		return;
	}

	//Parse Trace ID as Trace ID (int?)
	iTraceID = Int32.Parse(strID);

	Console.WriteLine("Trace Flag Source");
	strSource = Console.ReadLine();

	Console.WriteLine("Trace Flag Source Title");
	strTitle = Console.ReadLine();

	Console.WriteLine("Trace Flag Source URL");
	strURL = Console.ReadLine();


	//Instanciate Transaction Object to Serializable
	objTO = new TransactionOptions
	{
		IsolationLevel = System.Transactions.IsolationLevel.Serializable
	};

	using
	(

		//Instantiate Transaction Scope from Transaction Object
		objTS = new TransactionScope
					(
						   TransactionScopeOption.Required
						   , objTO
					)

	)
	{

                IQueryable<byte?> iQueryableSequenceNbrMax = dbDE.TVF_TraceFlagSource_GetSequenceNbrMax
                    (
                        traceID: iTraceID
                    )
                    ;

		byte? byteSequenceNbrMax = iQueryableSequenceNbrMax.FirstOrDefault();


		byteSequenceNbr = (byte)(byteSequenceNbrMax + 1);

		try
		{
			objTFS = new traceFlagSource();

			objTFS.id = (int)(iTraceID);
			objTFS.sequenceNbr = byteSequenceNbr;
			objTFS.status = true;
			objTFS.source = strSource;
			objTFS.title = strTitle;
			objTFS.url = strURL;

			objTFS.addedBy = getUser();
			objTFS.dateAdded = DateTime.Now;


			dbDE.traceFlagSources.Add(objTFS);

			dbDE.SaveChanges();

			//complete the transaction
			objTS.Complete();

		}
		catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
		{
			handleDBException(objTS, dbEx);
		}
		catch (System.Data.Entity.Infrastructure.DbUpdateException dbEx)
		{
			handleDBException(objTS, dbEx);
		}

		if (objTFS != null)
		{
			objTFS = null;
		}

	}

} //traceFlagSourceAdd



 

handleDBException

        private void handleDBException
        (
              TransactionScope objTS
            , System.Data.DataException dbEx
        )
        {
            //dispose the transaction
            objTS.Dispose();

            Exception raise = dbEx;

            messageBuffer = "";

            if (dbEx is System.Data.Entity.Validation.DbEntityValidationException)
            {

                messageBuffer = getDBExceptionMessage
                                (
                                    (System.Data.Entity.Validation.DbEntityValidationException)
                                    dbEx
                                );
            }
            else if (dbEx is System.Data.Entity.Infrastructure.DbUpdateException)
            {

                messageBuffer = getDBExceptionMessage
                                (
                                    (System.Data.Entity.Infrastructure.DbUpdateException)
                                    dbEx
                                );
            }


            colorForegroundSaved = Console.ForegroundColor;

            Console.ForegroundColor = ConsoleColor.Red;

            Console.WriteLine(messageBuffer);

            Console.ForegroundColor = colorForegroundSaved;

            // raise a new exception nesting
            // the current instance as InnerException
            //raise = new InvalidOperationException(messageBuffer, raise);

            //throw raise;

        }

 

getDBExceptionMessage // System.Data.Entity.Infrastructure.DbUpdateException


        private String getDBExceptionMessage
        (
            System.Data.Entity.Infrastructure.DbUpdateException dbEx
        )
        {

            strMessageBuilder.Clear();

            strMessage =
                String.Format
                    (
                          FORMAT_EXCEPTION_DB_INSERT_TRACE_SOURCE
                        , dbEx.Message
                        , dbEx.HResult
                    );

            strMessageBuilder.Append(strMessage);

            Exception updateException
                = (Exception) dbEx.InnerException;

            System.Data.SqlClient.SqlException sqlException
                = (System.Data.SqlClient.SqlException)updateException.InnerException;

            int iErrorNumber = 0;

            foreach (System.Data.SqlClient.SqlError error in sqlException.Errors)
            {

                iErrorNumber = iErrorNumber + 1;

                strMessageBuilder.AppendFormat
                        (
                              "{0} Inner Exception {1}\n"
                            , iErrorNumber
                            , error.Message
                        );
            }

            foreach (var result in dbEx.Entries)
            {
                strMessageBuilder.AppendFormat
                    (
                          "Type: {0} was part of the problem. \n"
                        , result.Entity.GetType().Name
                    );
            }

            return (strMessageBuilder.ToString());

        }


getDBExceptionMessage // System.Data.Entity.Validation.DbEntityValidationException

        private String getDBExceptionMessage
        (
            System.Data.Entity.Validation.DbEntityValidationException dbEx
        )
        {

            strMessageBuilder.Clear();

            foreach (var validationErrors in dbEx.EntityValidationErrors)
            {
                foreach (var validationError in validationErrors.ValidationErrors)
                {

                    string message = 
                            string.Format
                            (
                                "{0}:{1}"
                                , validationErrors.Entry.Entity.ToString()
                                , validationError.ErrorMessage
                            );

                    strMessageBuilder.Append(message + Environment.NewLine);


                }

            }

            return (strMessageBuilder.ToString());

        }


References

  1. StackOverflow
    • How to import a function with scalar return value in EF 5
      Link
    • InnerException in Entity Framework when I try add an entity
      Link
    • What is the best way to rollback a .net transaction?
      Link
  2. Microsoft
    • Microsoft – Developer
        • .NET Framework Class Library > System.Data Namespaces >  System.Data.SqlClient
          • SqlError Class
            Link
        • System.Data.SqlClient > SqlException Class > SqlException Properties
          • SqlException.Errors Property
            Link

Entity Framework – Query – “SELECT 1 AS [C1]” – Workfile & WorkTable

Background

Sent a couple of goodwill posts towards Entity Framework.

Ever the curmudgeon, it is time to start taking some shots.

 

Sample Generated Query

Query Snippet – Original

Here is a very small portion of one of the queries generated by Entity Framework.


select 1

where 
(
	NOT EXISTS
         (

		SELECT 1 AS [C1]
            
		FROM
                (

		    SELECT
 
		        [Extent10].[End_Id] AS [End_Id],
                        [Extent10].[FootnoteParent_Id] AS [FootnoteParent_Id],
                        [Extent11].[Id] AS [Id2],
                        [Extent12].[Id] AS [Id3],
                        [Extent12].[BeginDate] AS [BeginDate]
             
		   FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
				
		   INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
			ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
			AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
             
		  LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12] 
			ON [Extent10].[Begin_Id] = [Extent12].[Id]

            
		  WHERE [Extent10].[Container_Id] IS NOT NULL 

			
	      ) AS [Filter2]
          
	     LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
		   
		ON [Filter2].[End_Id] = [Extent13].[Id]

	) -- NOT EXISTS

) -- SELECT 1


Query Snippet – Revised

Here is a rewrite



select 1

where not exists 
(

	SELECT 
			1

	FROM [dbo].[UcNoteContainerFootnotes] AS [Extent10]
	
	INNER JOIN [dbo].[CourseUCTCA] AS [Extent11]
				 
		ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
	

) -- SELECT 1


Explanation

  1. We can discard is not null, due to the same columns being referenced in the “Inner Join
    • The Inner Join “ON ([Extent10].[Container_Id] = [Extent11].[NoteContainer_Id])
    • And, the where clause we can likely jettison are
      • AND ([Extent11].[NoteContainer_Id] IS NOT NULL)
      • WHERE  ( [Extent10].[Container_Id] IS NOT NULL )
  2. We can also likely discard the Left Outer Join, as they are quite not needed in a Not Exists Clause
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent12]
      • ON [Extent10].[Begin_Id] = [Extent12].[Id]
    • LEFT OUTER JOIN [dbo].[AcademicYears] AS [Extent13]
      • ON [Extent10].[End_Id] = [Extent13].[Id]

 

Obviously, we will have to test things out.

 

 

Query Plan

Query Snippet – Original

Query Snippet – Revised

Explanation

  1. Query Comparison
    • The Original Query is at 17%
    • And, the revised is at 83%
  2. Join Type
    • Original Query uses “Hash Join
      • Cost is 79%
    • And, the revised is “Nested Loops
      • Cost is 2%

 

Statistics I/O

Query Snippet – Original

 

Query Snippet – Revised

Tabulate

Table Original Query Revised Query
 UcNoteContainerFootnotes Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 CourseUCTCA  Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Workfile  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 WorkTable  Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 

Explanation

  • The reworked query appears to be less taxing in terms of IO
    • CourseUCTCA
      • Original Query :- Logical Reads is at 8
      • Revised Query :- Logical Reads is at 4
    • Workfile
      • Original Query because it is an Hash Join
    • WorkTable
      • Original Query, also because it is an Hash Join

 

Summary

In later posts, will talk more about WorkTable and Workfiles.

What they are and how to measure their cost.

And, very importantly, how to make sure code re-write is accurate.

Unfortunately, Entity Framework generated SQL Code can suffer from a little intemperance

 

Entity Framework – SQL Server/ Stored Procedure

Background

Let us look further into Entity Frameworks.

As Entity Framework supports the full repertoire of programmable objects in SQL Server, we can use Stored Procedures and Functions, in addition to Tables and Views.

 

Database

SQL Server

Diagram

Stored Procedure

Stored Procedure – dbo.usp_TraceFlagSourceFetch

if object_id('[dbo].[usp_TraceFlagSourceFetch]') is null
begin
	exec('create procedure [dbo].[usp_TraceFlagSourceFetch] as ');
end
go

alter procedure [dbo].[usp_TraceFlagSourceFetch] 
(
	@id int
)
as
begin

	select 
			  tblTFS.id
			, tblTFS.[sequenceNbr]
			, tblTFS.[status]
			, tblTFS.
			, tblTFS.[title]
			, tblTFS.[url]
			, tblTFS.[addedBy]
			, tblTFS.[dateAdded]
	from   [dbo].[traceFlagSource] tblTFS

	where tblTFS.id = @id

end
go

grant execute on [dbo].[usp_TraceFlagSourceFetch]   to [edmgen]
go


 

Visual Studio

Launch Visual Studio and open the Project.

Solution Explorer

Access the Solution Explorer and double click on the edmx file.

EDMX

Here is what our EDMX Diagram looks like.

 

Model Browser

Let us review the Model Browser.

Right click in an empty spot on the diagram panel and from the drop-down menu choose, Model Browser.

Diagram Panel – Menu

Model Browser Panel – What Is?

Access the “Model Browser“.

An edmx contains the following section, Diagrams, Conceptual model, and Data Store.

We have already covered the Diagram.

The Conceptual Model reflects the Object Components ( Entity Types, Complex Types, Enum Types, Association, and Function Imports ).

And, the last section is the Data Store.

 

Model Browser Panel – Original

 

Update Model

Let us update the model so that we can have access to the recently created or exposed Stored Procedure.

Update Model – Choose Your Database Objects and Settings

Before Creating \ Exposing

Here is what the “Update Wizard” looks like before exposing the Stored Procedure.

 

After Creating \ Exposing

Upon creating the SP, usp_TraceFlagSourceFetch, it shows up in the Update Wizard’s Add Tab.

 

Add the Database Stored Procedure

 

Model Browser Panel – Post Adding Database Object

Once we update our Database Model and choose to Add the Stored Procedure, we will revisit the Model Browser.

The pertinent areas are :

  1. Model
    • Function Imports
      • usp_TraceFlagSourceFetch
  2. Store
    • Stored procedures / Functions
      • usp_TraceFlagSourceFetch

 

Function Import – default

During the process of updating the model with the Stored Procedure, the Wizard also creates a default Function Import.

Model Browser – Model – Function Imports – usp_TraceFlagSourceFetch

Properties

Let us quickly review the properties of our auto-created Function Import

Menu

To access the property right click on the Function and select Properties from the dropdown menu.

Properties

Explanation
  1. Stored Procedure Name
    • We see the name of the  underlying Stored Procedure, usp.TraceFlagSourceFetch,
  2. Return Type
    • And, also notice that the system has auto created a Return Type
      • The Return Type is usp_TraceFlagSourceFetch_Result1

 

Properties – Return Type

We are able to access the “Return Type” and review the structure of hat is actually Returned

Edit Function Import

By default the Return is an auto-created type.

It’s naming syntax is <SP>_Result#.

 

Edit Function Import .. #2

We should click on the “Get Column Information” button to get the actual columns and their metadata.

 

Mapping Details

If we access the Model Browser and review the Mapping Details for our selected SP, here is what we see:

 

 

Add New – Function Import

When defining our Stored Procedure we made sure to include the same columns as the table that we are exposing.

And, in so doing, we can explicitly map the Return Type to our table.

Add New – Function Import – Menu

Right click in an empty area on the diagram model and choose the “Add New” \ “Function Import” menu entry.

 

Add New – Function Import – TraceFlagSourceFetch

Explanation

  1. Function Import Name
    • We offered a name and that name is TraceFlagSourceFetch
  2. Return a collection of
    • Entities
      • traceFlagSource

Add New – Function Import – TraceFlagSourceFetch – Get Column Information

Upon clicking on the “Get Column Information” button, we are able to review the list of columns.

 

Model Browser Panel – Post Adding a “Function Import”

Explanation

Quick review, we have two Function Imports:

  1. TraceFlagSourceFetch
    • The one we defined ourselves
  2. usp_TraceFlagSourceFetch
    • The one auto-created for us

 

 

Visual Studio

Launch Visual Studio and open the Project.

Solution Explorer

Access the Solution Explorer and double click on the edmx file.

EDMX

Here is what our EDMX Diagram looks like.

 

Model Browser

Let us review the Model Browser.

Right click in an empty spot on the diagram panel and from the drop-down menu choose, Model Browser.

Diagram Panel – Menu

Model Browser Panel – What Is?

Access the “Model Browser“.

An edmx contains the following section, Diagrams, Conceptual model, and Data Store.

We have already covered the Diagram.

The Conceptual Model reflects the Object Components ( Entity Types, Complex Types, Enum Types, Association, and Function Imports ).

And, the last section is the Data Store.

 

Model Browser Panel – Original

 

Update Model

Let us update the model so that we can have access to the recently created or exposed Stored Procedure.

Update Model – Choose Your Database Objects and Settings

Before Creating \ Exposing

Here is what the “Update Wizard” looks like before exposing the Stored Procedure.

 

After Creating \ Exposing

Upon creating the SP, usp_TraceFlagSourceFetch, it shows up in the Update Wizard’s Add Tab.

 

Add the Database Stored Procedure

 

Model Browser Panel – Post Adding Database Object

Once we update our Database Model and choose to Add the Stored Procedure, we will revisit the Model Browser.

The pertinent areas are :

  1. Model
    • Function Imports
      • usp_TraceFlagSourceFetch
  2. Store
    • Stored procedures / Functions
      • usp_TraceFlagSourceFetch

 

Function Import – default

During the process of updating the model with the Stored Procedure, the Wizard also creates a default Function Import.

Model Browser – Model – Function Imports – usp_TraceFlagSourceFetch

Properties

Let us quickly review the properties of our auto-created Function Import

Menu

To access the property right click on the Function and select Properties from the dropdown menu.

Properties

Explanation
  1. Stored Procedure Name
    • We see the name of the underlying Stored Procedure, usp.TraceFlagSourceFetch,
  2. Return Type
    • And, also notice that the system has auto created a Return Type
      • The Return Type is usp_TraceFlagSourceFetch_Result1

 

Properties – Return Type

We are able to access the “Return Type” and review the structure of hat is actually Returned

Edit Function Import

By default the Return is an auto-created type.

It’s naming syntax is <SP>_Result#.

 

Edit Function Import .. #2

We should click on the “Get Column Information” button to get the actual columns and their metadata.

 

Mapping Details

If we access the Model Browser and review the Mapping Details for our selected SP, here is what we see:

 

 

Add New – Function Import

When defining our Stored Procedure we made sure to include the same columns as the table that we are exposing.

And, in so doing, we can explicitly map the Return Type to our table.

Add New – Function Import – Menu

Right click in an empty area on the diagram model and choose the “Add New” \ “Function Import” menu entry.

 

Add New – Function Import – TraceFlagSourceFetch

Explanation

  1. Function Import Name
    • We offered a name and that name is TraceFlagSourceFetch
  2. Return a collection of
    • Entities
      • traceFlagSource

Add New – Function Import – TraceFlagSourceFetch – Get Column Information

Upon clicking on the “Get Column Information” button, we are able to review the list of columns.

 

Model Browser Panel – Post Adding a “Function Import”

Explanation

Quick review, we have two Function Imports:

  1. TraceFlagSourceFetch
    • The one we defined ourselves
  2. usp_TraceFlagSourceFetch
    • The one auto-created for us

 

Code

We have to stub codes to share:

The first one calls the Function created by default.

And, the other one calls the function we created and casted to our Table.

Code – Default Function


private void listTraceFlagSourceUseSPImplicit(int iID)
{ 

	//invoke the default Function
	var varTFS = dbDE.usp_TraceFlagSourceFetch(iID);

	//Iterate list
	//Remember that it Returns data as a usp_TraceFlagSourceFetch_Result1
	foreach (usp_TraceFlagSourceFetch_Result1 objTFS in varTFS)
	{

		// prepare UI output
		strLog
			= String.Format
				(
					  FORMAT_TRACESOURCE_LIST
					, objTFS.source
					, objTFS.title
					, objTFS.url
				);

		Console.WriteLine(strLog);

		// Output Line Break
		Console.WriteLine(CHAR_LINEBREAK);

   } //foreach


} //listTraceFlagSourceUseSPImplicit

Code – Explicit Function


public void listTraceFlagSourceUseSPExplicit(int iID)
{

	//invoke the explicit Function
	//Remember that the data return is strongly type
	//and it is a table
	var varTFS = dbDE.TraceFlagSourceFetch(iID);

	//Iterate list
	//Using foreach expose each list element in varTF
	foreach (traceFlagSource objTFS in varTFS)
	{

		// prepare UI output
		strLog
			= String.Format
				(
					  FORMAT_TRACESOURCE_LIST
					, objTFS.source
					, objTFS.title
					, objTFS.url
				);

		Console.WriteLine(strLog);

		// Output Line Break
		Console.WriteLine(CHAR_LINEBREAK);

	} //foreach
	


} //listTraceFlagSourceUseSPExplict


Sql Server Profiler

Let us target SQL Server Profiler and see what our payload looks like:

Explanation:

  1. Event Class
    • RPC:Completed
    • TextData
      • Sample
        • exec [dbo].[usp_TraceFlagSourceFetch] @id=2559
    • CPU
      • 0
    • Reads
      • 2
    • Writes
      • 0

 

Listening

Behind every post there is a story.

But like Rick, this is how I will have to end this Story.

Slick Rick – Bedtime Story
Link

He dropped the gun, so went the glory
And this is the way I have end this story

Just another case ’bout the wrong path,
Straight ‘n narrow or yo’ soul gets cast(?)