LinqToXML removing empty xmlns attributes

Suppose you need to generate the following XML:

<GenevaLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.advent.com/SchemaRevLevel401/Geneva masterschema.xsd" xmlns="http://www.advent.com/SchemaRevLevel401/Geneva">
  <PriceRecords>
    <PriceRecord>
    </PriceRecord>
  </PriceRecords>
</GenevaLoader>

Normally you would write the following C# code to accomplish this:

const string ns = "http://www.advent.com/SchemaRevLevel401/Geneva";
XNamespace xnsp = ns;
XNamespace xsi = XNamespace.Get("http://www.w3.org/2001/XMLSchema-instance");

XElement root = new XElement( xnsp + "GenevaLoader",  
  new XAttribute(XNamespace.Xmlns + "xsi", xsi.NamespaceName), 
  new XAttribute( xsi + "schemaLocation", "http://www.advent.com/SchemaRevLevel401/Geneva masterschema.xsd"));

XElement priceRecords = new XElement("PriceRecords");
root.Add(priceRecords);

	for(int i = 0; i < 3; i++)
	{
	    XElement price = new XElement("PriceRecord");
	   priceRecords.Add(price);
	}

	doc.Save("geneva.xml");

The problem with this approach is that it adds a additional empty xmlns arrtribute on the “PriceRecords” element, like so :

<GenevaLoader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.advent.com/SchemaRevLevel401/Geneva masterschema.xsd" xmlns="http://www.advent.com/SchemaRevLevel401/Geneva">
  <PriceRecords xmlns="">
    <PriceRecord>
    </PriceRecord>
  </PriceRecords>
</GenevaLoader>

The solution is to add the xmlns NameSpace in code to each child and grandchild elements of the root element like so :

XElement priceRecords = new XElement( xnsp + "PriceRecords");
root.Add(priceRecords);

for(int i = 0; i < 3; i++)
{
    XElement price = new XElement(xnsp + "PriceRecord");
   priceRecords.Add(price);
}

Copying files from GAC using xcopy or Windows Explorer

use this command for copying files using a wildcard from the GAC to a local folder.
xcopy c:\windows\assembly\Microsoft.SqlServer.Smo*.dll c:\gacdll /s/r/y/c

The above command will continue even it encounters any “Access Denied” errors, thus copying over the required files.

To copy files using the Windows explorer just disable the GAC Cache Viewer by adding a entry to the registry:
Browse to “HKEY_LOCALMACHINE\Software\Microsoft\Fusion”
Add a Dword called DisableCacheViewer. Set the value of it to 1.

Entity Framework v1 – tips and Tricks Part 3

The previous posts on Entity framework are available here :
Entity Framework version 1- Brief Synopsis and Tips – Part 1
Entity Framework v1 … Brief Synopsis and Tips – Part 2

General Tips on Entity Framework v1 & Linq to Entities:

ToTraceString()

If you need to know the underlying SQL that the EF generates for a Linq To Entities query, then use the ToTraceString() method of the ObjectQuery class. (or use LINQPAD)
Note that you need to cast the LINQToEntities query to ObjectQuery before calling TotraceString() as follows:

string efSQL = ((ObjectQuery)from c in ctx.Contact
         where c.Address.Any(a => a.CountryRegion == "US")
         select c.ContactID).ToTraceString();

======================================================================

MARS or MultipleActiveResultSet
When you create a EDM Model (EDMX file) from the database using Visual Studio, it generates a connection string with the same name as the name of the EntityContainer in CSDL. In the ConnectionString so generated it sets the MultipleActiveResultSet attribute to true by default. So if you are running the following query then it streams multiple readers over the same connection:

using (BAEntities context = new BAEntities())
{
  var cons =
      from con in context.Contacts
      where con.FirstName == "Jose"
      select con;
  foreach (var c in cons)
  {
    if (c.AddDate < new System.DateTime(2007, 1, 1))
    {
      c.Addresses.Load();
    }
  }
}

======================================================================
Explicitly opening and closing EntityConnection

When you call ToList() or foreach on a LINQToEntities query the EF automatically closes the connection after all the records from the query have been consumed.

Thus if you need to run many LINQToEntities queries over the same connection then explicitly open and close the connection as follows:

using (BAEntities context = new BAEntities())
{
  context.Connection.Open();
  var cons = from con in context.Contacts where con.FirstName == "Jose"
             select con;
  var conList = cons.ToList();
  var allCustomers =  from con in context.Contacts.OfType<Customer>()
                      select con;
  var allcustList = allCustomers.ToList();
  context.Connection.Close();
}

======================================================================

Dispose ObjectContext only if required

After you retrieve entities using the ObjectContext and you are not explicitly disposing the ObjectContext then insure that your code does consume all the records from the LinqToEntities query by calling .ToList() or foreach statement, otherwise the the database connection will remain open and will be closed by the garbage collector when it gets to dispose the ObjectContext.

Secondly if you are making updates to the entities retrieved using LinqToEntities then insure that you dont inadverdently dispose of the ObjectContext after the entities are retrieved and before calling .SaveChanges() since you need the SAME ObjectContext to keep track of changes made to the Entities (by using ObjectStateEntry objects). So if you do need to explicitly dispose of the ObjectContext do so only after calling SaveChanges() and only if you dont need to change track the entities retrieved any further.


======================================================================

SQL InjectionAttacks under control with EFv1

LinqToEntities and LinqToSQL queries are parameterized before they are sent to the DB hence they are not vulnerable to SQL Injection attacks.


EntitySQL may be slightly vulnerable to attacks since it does not use parameterized queries. However since the EntitySQL demands that the query be valid Entity SQL syntax and valid native SQL syntax at the same time.


So the only way one can do a SQLInjection Attack is by knowing the SSDL of the EDM Model and be able to write the correct EntitySQL (note one cannot append regular SQL since then the query wont be a valid EntitySQL syntax) and append it to a parameter.

======================================================================
Improving Performance
You can convert the EntitySets and AssociationSets in a EDM Model into precompiled Views using the edmgen utility. for e.g. the Customer Entity can be converted into a precompiled view using edmgen and all LinqToEntities query against the contaxt.Customer EntitySet will use the precompiled View instead of the EntitySet itself (the same being true for relationships (EntityReference & EntityCollections of a Entity)). The advantage being that when using precompiled views the performance will be much better.

The syntax for generating precompiled views for a existing EF project is :
edmgen /mode:ViewGeneration /inssdl:BAModel.ssdl /incsdl:BAModel.csdl /inmsl:BAModel.msl /p:Chap14.csproj
Note that this will only generate precompiled views for EntitySets and Associations and not for existing LinqToEntities queries in the project.(for that use CompiledQuery.Compile<>)
Secondly if you have a LinqToEntities query that you need to run multiple times, then one should precompile the query using CompiledQuery.Compile method. The CompiledQuery.Compile<> method accepts a lamda expression as a parameter, which denotes the LinqToEntities query  that you need to precompile.

The following is a example of a lamda that we can pass into the CompiledQuery.Compile() method

Expression<Func<BAEntities, string, IQueryable<Customer>>> expr = (BAEntities ctx1, string loc) =>
                    from c in ctx1.Contacts.OfType<Customer>()
                    where c.Reservations.Any(r => r.Trip.Destination.DestinationName == loc)
                    select c;

Then we call the Compiled Query as follows:

var query = CompiledQuery.Compile<BAEntities, string, IQueryable<Customer>>(expr);

using (BAEntities ctx = new BAEntities())
{
    var loc = "Malta";
    IQueryable<Customer> custs = query.Invoke(ctx, loc);
    var custlist = custs.ToList();
    foreach (var item in custlist)
    {
        Console.WriteLine(item.FullName);
    }
}

Note that if you created a ObjectQuery or a Enitity SQL query instead of the LINQToEntities query, you dont need precompilation for e.g.

An Example of EntitySQL query :
string esql = "SELECT VALUE c from Contacts AS c where c is of(BAGA.Customer) and c.LastName = 'Gupta'";
ObjectQuery<Customer> custs = CreateQuery<Customer>(esql);

An Example of ObjectQuery built using ObjectBuilder methods:
from c in Contacts.OfType<Customer>().Where("it.LastName == 'Gupta'")
select c

This is since the Query plan is cached and thus the performance improves a bit, however since the ObjectQuery or EntitySQL query still needs to materialize the results into Entities hence it will take the same amount of performance hit as with LinqToEntities.

However note that not ALL EntitySQL based or QueryBuilder based ObjectQuery plans are cached. So if you are in doubt always create a LinqToEntities compiled query and use that instead


============================================================
GetObjectStateEntry Versus GetObjectByKey

We can get to the Entity being referenced by the ObjectStateEntry via its Entity property and there are helper methods in the ObjectStateManager (osm.TryGetObjectStateEntry) to get the ObjectStateEntry for a entity (for which we know the EntityKey). Similarly The ObjectContext has helper methods to get an Entity i.e. TryGetObjectByKey().

TryGetObjectByKey() uses GetObjectStateEntry method under the covers to find the object, however One important difference between these 2 methods is that TryGetObjectByKey queries the database if it is unable to find the object in the context, whereas TryGetObjectStateEntry only looks in the context for existing entries. It will not make a trip to the database
=============================================================

POCO objects with EFv1:
To create POCO objects that can be used with EFv1. We need to implement 3 key interfaces:

IEntityWithKey
IEntityWithRelationships
IEntityWithChangeTracker

Implementing IEntityWithKey is not mandatory, but if you dont then we need to explicitly provide values for the EntityKey for various functions (for e.g. the functions needed to implement IEntityWithChangeTracker and IEntityWithRelationships).

Implementation of IEntityWithKey involves exposing a property named EntityKey which returns a EntityKey object.
Implementation of IEntityWithChangeTracker involves implementing a method named SetChangeTracker since there can be multiple changetrackers (Object Contexts) existing in memory at the same time.

public void SetChangeTracker(IEntityChangeTracker changeTracker)
{
  _changeTracker = changeTracker;
}

Additionally each property in the POCO object needs to notify the changetracker (objContext) that it is updating itself by calling the EntityMemberChanged and EntityMemberChanging methods on the changeTracker. for e.g.:

public EntityKey EntityKey
{
  get { return _entityKey; }
  set
  {
    if (_changeTracker != null)
    {
       _changeTracker.EntityMemberChanging("EntityKey");
       _entityKey = value;
       _changeTracker.EntityMemberChanged("EntityKey");
     }
     else
       _entityKey = value;
  }
}
=====================  Custom Property  ====================================

[EdmScalarPropertyAttribute(IsNullable = false)]
public System.DateTime OrderDate
{
  get { return _orderDate; }
  set
  {
    if (_changeTracker != null)
    {
      _changeTracker.EntityMemberChanging("OrderDate");
      _orderDate = value;
      _changeTracker.EntityMemberChanged("OrderDate");
    }
    else
      _orderDate = value;
  }
}

Finally you also need to create the EntityState property as follows:

public EntityState EntityState
{
  get  { return _changeTracker.EntityState;  }
}

The IEntityWithRelationships involves creating a property that returns RelationshipManager object:

public RelationshipManager RelationshipManager
{
  get
  {
    if (_relManager == null)
      _relManager = RelationshipManager.Create(this);
    return _relManager;
  }
}

============================================================

Tip : ProviderManifestToken – change EDMX File to use SQL 2008 instead of SQL 2005

To use with SQL Server 2008, edit the EDMX file (the raw XML) changing the ProviderManifestToken in the SSDL attributes from "2005" to "2008"
=============================================================
With EFv1 we cannot use Structs to replace a anonymous Type while doing projections in a LINQ to Entities query. While the same is supported with LINQToSQL, it is not with LinqToEntities. For e.g. the following is not supported with LinqToEntities since only parameterless constructors and initializers are supported in LINQ to Entities. (the same works with LINQToSQL)

public struct CompanyInfo
{
    public int ID { get; set; }
    public string Name { get; set; }
}
var companies = (from c in dc.Companies
            where c.CompanyIcon == null
             select new CompanyInfo { Name = c.CompanyName, ID = c.CompanyId }).ToList(); ;

Split SQL 2005 column values into multiple rows

Suppose that you have a column that stored multiple values in a single row using some delimiter as ‘,’. Now you are tasked with splitting these values into a separate row for each value and you need to do this on multiple rows in the table.

   1: select distinct r.items

   2: from Order o

   3: cross apply dbo.split(o.Products,',') r

Here the Order Table has a Products column that has multiple Products stored in a single column separated with commas like ‘Tea,Coffee’ associated with a single Order. What the above query does is it gives a distinct list of names of all Products stored in all rows in the Order table. The Split() is a UDF which splits the string into one row for each value

Similarly if you need to check if these multiple values stored in the Products column contain a particular string from a list of values, i,e,

   1: select o.* from Company c

   2: join Order o on c.ItemID = o.companyItemID

   3: where (select * from dbo.split(o.Products) in ('TBD','Tea', 'Coffee'))

Since the above would not compile hence the right solution to do something like this would be:

   1: select o.* from Company c

   2: join Order o on c.ItemID = o.companyItemID

   3: cross apply dbo.Split(Products,',') p 

   4: where p.Items in ('TBD','Tea','Coffee')

what we are doing here is a inner join on the Split() table valued UDF which returns a table containing one row for each value split and then comparing each Item to see if it matches one of the product names i.e. Tea or Coffee.

Dispose and Finalize methods and .NET Memory optimizations

I have jotted down a few points on implementing the IDisposable and Finalize patterns in .NET.

Please find it here : Dispose and Finalize methods

I also jotted down some .net Memory Optimization tips: Please find them here : Some Tips on .NET Memory optimizations

Creating dynamically generated logfiles with log4net

If you need to generate logfile names dynmically when using log4net then we can do this using the log4net Properties.

To learn more on this please visit : creating dynamic logfile names with log4net

Tips on using log4net RollingFileAppender

I have compiled a list of useful properties that you can set on the RollingFileAppender in the log4net XML config file which can determine the order in which log file backups will be generated, whether they will rollover based on dates or size or both.

For more information please check this link out : Tips on using log4net RollingFileAppender

ConfigSectionHandler for Hierarchical configs

Here is a quick way to create a ConfigSection Handler for reading Hierarchical configs. For e.g. suppose you need to read a config file which has the following structure:

MySpace
 
  23
   Rohit
  Gupta
  

For this you would create the 2 classes, one for the Parent Config and another for the chold config like this:

  [XmlRoot("MainConfig")]
   public class MainConfig
   {
       private static readonly MainConfig instance =
 (MainConfig)ConfigurationManager.GetSection("MainConfig");
       public static MainConfig GetInstance()
       {
           if (instance == null)
               throw new ConfigurationErrorsException(
                   "Unable to locate or deserialize the 'MainConfig' section.");

           return instance;
       }

       public string Company { get; set; }

       public SubConfig FriendsConfig { get; set; }
   }

   [XmlRoot("SubConfig")]
   public class SubConfig
   {
       public int ID { get; set; }
       public string Name { get; set; }
       public string LastName { get; set; }
   }

Then you would create a configsectionHandler class which reads the config from the .config file:

public class MainConfigSectionHandler : IConfigurationSectionHandler
   {
       #region IConfigurationSectionHandler Members

       public object Create(object parent, object configContext, System.Xml.XmlNode section)
       {
           MainConfig typedConfig = GetConfig(section);

           if (typedConfig != null)
           {
               #region Get Sub Configs
               foreach (XmlNode node in section.ChildNodes)
               {
                   switch (node.Name)
                   {
                       case "SubConfig":
                           SubConfig friendsConfig = GetConfig(node);
                           typedConfig.FriendsConfig = friendsConfig;
                           break;
                       default:
                           break;
                   }
               }
               #endregion
           }


           return typedConfig;
       }

       public T GetConfig(System.Xml.XmlNode section) where T : class
       {
           T sourcedObject = default(T);
           Type t = typeof(T);
           XmlSerializer ser = new XmlSerializer(typeof(T));
           sourcedObject = ser.Deserialize(new XmlNodeReader(section)) as T;
           return sourcedObject;
       }

       #endregion

   }

After this you would add a entry in the app.config for this configsection Handler as the following:

Finally to read this config, you would write the following:

MainConfig config = MainConfig.GetInstance(); Console.WriteLine(config.Company);
Console.WriteLine(config.FriendsConfig.ID);
Console.WriteLine(config.FriendsConfig.LastName);
Console.WriteLine(config.FriendsConfig.Name);