Data Access Tracing in .NET (Universal for any provider)

Sometimes I'm surprised to see in .NET some very obvious, required functionality not available. I have to implement it by my own. When you develop database-driven web application you face a lot of issues and you need to diagnose if the issue is in database logic or on middle tier or on client. You can trace client and middle tier easily, but for some reason Microsoft thinks that you don't need the same with database access. Solution they provide to trace SQL requests are supercomplex and heavy (SQL Extended Events,...).

So I had to spend couple of days to research and implement generic DbConnection tracing approach that supposed to work with any DbProvider. This approach should work with direct DbConnection as well as with ORM tools (EntityFramework). The idea is to replace standard DbProviderFactories with a wrappers, that will trace all necessary information. The Idea was taken from Glimpse.Ado.

Here is the code:

And here is what you need to add to web.config:

In application initialization module add line:
TraceableDbProviderFactory.IsEnabled = true;

Enjoy! 

Entity Framework Extensions to execute scalar queries (.First(), .Any(), .Count(), ...)

If you have used LINQ to SQL, Entity Framework you might noticed that when you use queryable extensions returning set of objects (like .Select(), .Where(), .Join(), Group...) you get defered-executable queries that will be converted to SQL end executed during first demand. You can combine those queries, join them together, and it eventually will be still one SQL request. However you don't get the same when you work with functions returning not Enumerable, but actual value (like .First(), .Any(), .Max(), ...). So thanks to people from Stackoverflow, here are extensions for DbContext and DbQuery (Entity Framework 5), that will allow you to execute those operations still getting back Queryable results:
Here are examples, how to use this code:
SQL generated for each statement is the same:

T4 for generating composable functions for DbContext of Entity Framework 5 model

If you try to Import Function which is composable using EF designer you will see error message :
Function imports cannot be created for composable functions.

However you can see that all data for those functions are imported to Storage space of the model. So there is enough information to generate functions wrappers automatically using T4 Template.
The following T4 template is modified version of original EF5 Db Context T4 template - it only generates wrappers for composable functions code to your partial db context class. I didn't test it for different scenarios, so this is the raw version of T4 template:

<#@ template language="C#" debug="false" hostspecific="true"#>
<#@ include file="EF.Utility.CS.ttinclude"#><#@
 output extension=".cs"#><#
 
const string inputFile = @"Model1.edmx";
var textTransformation = this;
var textTransform = DynamicTextTransformation.Create(textTransformation);
var code = new CodeGenerationTools(textTransformation);
var ef = new MetadataTools(textTransformation);
var typeMapper = new TypeMapper(code, ef, textTransform.Errors);
var loader = new EdmMetadataLoader(textTransform.Host, textTransform.Errors);
var itemCollection = loader.CreateEdmItemCollection(inputFile);
var modelNamespace = loader.GetModelNamespace(inputFile);
var codeStringGenerator = new CodeStringGenerator(code, typeMapper, ef);
var container = itemCollection.OfType<EntityContainer>().FirstOrDefault();
 
 
var mdl = new MetadataLoader(textTransformation);
MetadataWorkspace ws = null;
mdl.TryLoadAllMetadata(inputFile,out ws);
var storeItems = ws.GetItemCollection(DataSpace.SSpace);
var functions = storeItems.OfType<EdmFunction>().Where(x=>x.NamespaceName!="SqlServer" && typeMapper.IsComposable(x)).ToArray();
#>
//------------------------------------------------------------------------------
// <auto-generated>
// <#=GetResourceString("Template_GeneratedCodeCommentLine1")#>
//
// <#=GetResourceString("Template_GeneratedCodeCommentLine2")#>
// <#=GetResourceString("Template_GeneratedCodeCommentLine3")#>
// </auto-generated>
//------------------------------------------------------------------------------

<#
 
var codeNamespace = code.VsNamespaceSuggestion();
if (!String.IsNullOrEmpty(codeNamespace))
{
#>
namespace <#=code.EscapeNamespace(codeNamespace)#>
{
<#
    PushIndent("    ");
}
 
#>
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
<#
if (container.FunctionImports.Any())
{
#>
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
<#
}
#>
<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext
{
 
 <#
    foreach (var edmFunction in functions)
    {
        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);
    }
#>
}
<#
 
if (!String.IsNullOrEmpty(codeNamespace))
{
    PopIndent();
#>
}
<#
}
#>
<#+
 
 
 
private void WriteFunctionImport(TypeMapper typeMapper, CodeStringGenerator codeStringGenerator, EdmFunction edmFunction, string modelNamespace, bool includeMergeOption)
{
#>
 
    [EdmFunction("<#=edmFunction.NamespaceName#>", "<#=edmFunction.Name#>")]
    <#=codeStringGenerator.ComposableStoreFunctionMethod(edmFunction, modelNamespace)#>
    {
<#+
        codeStringGenerator.WriteStoreFunctionParameters(edmFunction, WriteFunctionParameter);
#>
        <#=codeStringGenerator.ComposableCreateStoreQuery(edmFunction, modelNamespace)#>
    }
<#+
 
}
 
 
 
public void WriteFunctionParameter(string name, string isNotNull, string notNullInit, string nullInit)
{
#>
        var <#=name#> = <#=isNotNull#> ?
            <#=notNullInit#> :
            <#=nullInit#>;
 
<#+
}
 
public const string TemplateId = "CSharp_DbContext_Context_EF5";
 
public class CodeStringGenerator
{
 
 public string ComposableCreateStoreQuery(EdmFunction edmFunction, string modelNamespace)
    {
        var parameters = _typeMapper.GetParameters(edmFunction);
 
        return string.Format(
            CultureInfo.InvariantCulture,
            "return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<{0}>(\"select [{1}].[{2}]({3})\"{4});",
            _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace),
            //edmFunction.NamespaceName,
            edmFunction.MetadataProperties["Schema"].Value,
            edmFunction.Name,
            string.Join(", ", parameters.Select(p => "@" + p.EsqlParameterName).ToArray()),
            _code.StringBefore(", ", string.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray())));
    }
 
    public void WriteStoreFunctionParameters(EdmFunction edmFunction, Action<string, string, string, string> writeParameter)
    {
        var parameters = FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef);
        foreach (var parameter in parameters.Where(p => p.NeedsLocalVariable))
        {
            var isNotNull = parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null";
            var notNullInit = "new SqlParameter(\"" + parameter.EsqlParameterName + "\", " + parameter.FunctionParameterName + ")";
            var nullInit = "new SqlParameter(\"" + parameter.EsqlParameterName + "\", typeof(" + parameter.RawClrTypeName + "))";
            writeParameter(parameter.LocalVariableName, isNotNull, notNullInit, nullInit);
        }
    }
 
 
    public string ComposableStoreFunctionMethod(EdmFunction edmFunction, string modelNamespace)
    {
        var parameters = _typeMapper.GetParameters(edmFunction);
        
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} ObjectResult<{1}> {2}({3})",
            AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)),
            _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace),
            _code.Escape(edmFunction),
            string.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray()));
    }
 
 
    private readonly CodeGenerationTools _code;
    private readonly TypeMapper _typeMapper;
    private readonly MetadataTools _ef;
 
    public CodeStringGenerator(CodeGenerationTools code, TypeMapper typeMapper, MetadataTools ef)
    {
        ArgumentNotNull(code, "code");
        ArgumentNotNull(typeMapper, "typeMapper");
        ArgumentNotNull(ef, "ef");
 
        _code = code;
        _typeMapper = typeMapper;
        _ef = ef;
    }
 
    public string Property(EdmProperty edmProperty)
    {
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} {1} {2} {{ {3}get; {4}set; }}",
            Accessibility.ForProperty(edmProperty),
            _typeMapper.GetTypeName(edmProperty.TypeUsage),
            _code.Escape(edmProperty),
            _code.SpaceAfter(Accessibility.ForGetter(edmProperty)),
            _code.SpaceAfter(Accessibility.ForSetter(edmProperty)));
    }
 
    public string NavigationProperty(NavigationProperty navigationProperty)
    {
        var endType = _typeMapper.GetTypeName(navigationProperty.ToEndMember.GetEntityType());
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} {1} {2} {{ {3}get; {4}set; }}",
            AccessibilityAndVirtual(Accessibility.ForProperty(navigationProperty)),
            navigationProperty.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many ? ("ICollection<" + endType + ">") : endType,
            _code.Escape(navigationProperty),
            _code.SpaceAfter(Accessibility.ForGetter(navigationProperty)),
            _code.SpaceAfter(Accessibility.ForSetter(navigationProperty)));
    }
    
    public string AccessibilityAndVirtual(string accessibility)
    {
        return accessibility + (accessibility != "private" ? " virtual" : "");
    }
    
    public string EntityClassOpening(EntityType entity)
    {
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} {1}partial class {2}{3}",
            Accessibility.ForType(entity),
            _code.SpaceAfter(_code.AbstractOption(entity)),
            _code.Escape(entity),
            _code.StringBefore(" : ", _typeMapper.GetTypeName(entity.BaseType)));
    }
    
    public string EnumOpening(SimpleType enumType)
    {
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} enum {1} : {2}",
            Accessibility.ForType(enumType),
            _code.Escape(enumType),
            _code.Escape(_typeMapper.UnderlyingClrType(enumType)));
        }
    
    public void WriteFunctionParameters(EdmFunction edmFunction, Action<string, string, string, string> writeParameter)
    {
        var parameters = FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef);
        foreach (var parameter in parameters.Where(p => p.NeedsLocalVariable))
        {
            var isNotNull = parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null";
            var notNullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\", " + parameter.FunctionParameterName + ")";
            var nullInit = "new ObjectParameter(\"" + parameter.EsqlParameterName + "\", typeof(" + parameter.RawClrTypeName + "))";
            writeParameter(parameter.LocalVariableName, isNotNull, notNullInit, nullInit);
        }
    }
    
    public string ComposableFunctionMethod(EdmFunction edmFunction, string modelNamespace)
    {
        var parameters = _typeMapper.GetParameters(edmFunction);
        
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} IQueryable<{1}> {2}({3})",
            AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)),
            _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace),
            _code.Escape(edmFunction),
            string.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray()));
    }
    
    public string ComposableCreateQuery(EdmFunction edmFunction, string modelNamespace)
    {
        var parameters = _typeMapper.GetParameters(edmFunction);
        
        return string.Format(
            CultureInfo.InvariantCulture,
            "return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<{0}>(\"[{1}].[{2}]({3})\"{4});",
            _typeMapper.GetTypeName(_typeMapper.GetReturnType(edmFunction), modelNamespace),
            edmFunction.NamespaceName,
            edmFunction.Name,
            string.Join(", ", parameters.Select(p => "@" + p.EsqlParameterName).ToArray()),
            _code.StringBefore(", ", string.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray())));
    }
    
    public string FunctionMethod(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption)
    {
        var parameters = _typeMapper.GetParameters(edmFunction);
        var returnType = _typeMapper.GetReturnType(edmFunction);
 
        var paramList = String.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray());
        if (includeMergeOption)
        {
            paramList = _code.StringAfter(paramList, ", ") + "MergeOption mergeOption";
        }
 
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} {1} {2}({3})",
            AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction)),
            returnType == null ? "int" : "ObjectResult<" + _typeMapper.GetTypeName(returnType, modelNamespace) + ">",
            _code.Escape(edmFunction),
            paramList);
    }
    
    public string ExecuteFunction(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption)
    {
        var parameters = _typeMapper.GetParameters(edmFunction);
        var returnType = _typeMapper.GetReturnType(edmFunction);
 
        var callParams = _code.StringBefore(", ", String.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()));
        if (includeMergeOption)
        {
            callParams = ", mergeOption" + callParams;
        }
        
        return string.Format(
            CultureInfo.InvariantCulture,
            "return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction{0}(\"{1}\"{2});",
            returnType == null ? "" : "<" + _typeMapper.GetTypeName(returnType, modelNamespace) + ">",
            edmFunction.Name,
            callParams);
    }
    
    public string DbSet(EntitySet entitySet)
    {
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} DbSet<{1}> {2} {{ get; set; }}",
            Accessibility.ForReadOnlyProperty(entitySet),
            _typeMapper.GetTypeName(entitySet.ElementType),
            _code.Escape(entitySet));
    }
 
    public string UsingDirectives(bool inHeader, bool includeCollections = true)
    {
        return inHeader == string.IsNullOrEmpty(_code.VsNamespaceSuggestion())
            ? string.Format(
                CultureInfo.InvariantCulture,
                "{0}using System;{1}" +
                "{2}",
                inHeader ? Environment.NewLine : "",
                includeCollections ? (Environment.NewLine + "using System.Collections.Generic;") : "",
                inHeader ? "" : Environment.NewLine)
            : "";
    }
}
 
public class TypeMapper
{
    private const string ExternalTypeNameAttributeName = @"http://schemas.microsoft.com/ado/2006/04/codegeneration:ExternalTypeName";
 
    private readonly System.Collections.IList _errors;
    private readonly CodeGenerationTools _code;
    private readonly MetadataTools _ef;
 
    public TypeMapper(CodeGenerationTools code, MetadataTools ef, System.Collections.IList errors)
    {
        ArgumentNotNull(code, "code");
        ArgumentNotNull(ef, "ef");
        ArgumentNotNull(errors, "errors");
 
        _code = code;
        _ef = ef;
        _errors = errors;
    }
 
    public string GetTypeName(TypeUsage typeUsage)
    {
        return typeUsage == null ? null : GetTypeName(typeUsage.EdmType, _ef.IsNullable(typeUsage), modelNamespace: null);
    }
 
    public string GetTypeName(EdmType edmType)
    {
        return GetTypeName(edmType, isNullable: null, modelNamespace: null);
    }
 
    public string GetTypeName(TypeUsage typeUsage, string modelNamespace)
    {
        return typeUsage == null ? null : GetTypeName(typeUsage.EdmType, _ef.IsNullable(typeUsage), modelNamespace);
    }
 
    public string GetTypeName(EdmType edmType, string modelNamespace)
    {
        return GetTypeName(edmType, isNullable: null, modelNamespace: modelNamespace);
    }
 
    public string GetTypeName(EdmType edmType, bool? isNullable, string modelNamespace)
    {
        if (edmType == null)
        {
            return null;
        }
 
        var collectionType = edmType as CollectionType;
        if (collectionType != null)
        {
            return String.Format(CultureInfo.InvariantCulture, "ICollection<{0}>", GetTypeName(collectionType.TypeUsage, modelNamespace));
        }
 
        var typeName = _code.Escape(edmType.MetadataProperties
                                .Where(p => p.Name == ExternalTypeNameAttributeName)
                                .Select(p => (string)p.Value)
                                .FirstOrDefault())
            ?? (modelNamespace != null && edmType.NamespaceName != modelNamespace ?
                _code.CreateFullName(_code.EscapeNamespace(edmType.NamespaceName), _code.Escape(edmType)) :
                _code.Escape(edmType));
 
        if (edmType is StructuralType)
        {
            return typeName;
        }
 
        if (edmType is SimpleType)
        {
            var clrType = UnderlyingClrType(edmType);
            if (!IsEnumType(edmType))
            {
                typeName = _code.Escape(clrType);
            }
 
            return clrType.IsValueType && isNullable == true ?
                String.Format(CultureInfo.InvariantCulture, "Nullable<{0}>", typeName) :
                typeName;
        }
 
        throw new ArgumentException("edmType");
    }
    
    public Type UnderlyingClrType(EdmType edmType)
    {
        ArgumentNotNull(edmType, "edmType");
 
        var primitiveType = edmType as PrimitiveType;
        if (primitiveType != null)
        {
            return primitiveType.ClrEquivalentType;
        }
 
        if (IsEnumType(edmType))
        {
            return GetEnumUnderlyingType(edmType).ClrEquivalentType;
        }
 
        return typeof(object);
    }
    
    public object GetEnumMemberValue(MetadataItem enumMember)
    {
        ArgumentNotNull(enumMember, "enumMember");
        
        var valueProperty = enumMember.GetType().GetProperty("Value");
        return valueProperty == null ? null : valueProperty.GetValue(enumMember, null);
    }
    
    public string GetEnumMemberName(MetadataItem enumMember)
    {
        ArgumentNotNull(enumMember, "enumMember");
        
        var nameProperty = enumMember.GetType().GetProperty("Name");
        return nameProperty == null ? null : (string)nameProperty.GetValue(enumMember, null);
    }
 
    public System.Collections.IEnumerable GetEnumMembers(EdmType enumType)
    {
        ArgumentNotNull(enumType, "enumType");
 
        var membersProperty = enumType.GetType().GetProperty("Members");
        return membersProperty != null 
            ? (System.Collections.IEnumerable)membersProperty.GetValue(enumType, null)
            : Enumerable.Empty<MetadataItem>();
    }
    
    public bool EnumIsFlags(EdmType enumType)
    {
        ArgumentNotNull(enumType, "enumType");
        
        var isFlagsProperty = enumType.GetType().GetProperty("IsFlags");
        return isFlagsProperty != null && (bool)isFlagsProperty.GetValue(enumType, null);
    }
 
    public bool IsEnumType(GlobalItem edmType)
    {
        ArgumentNotNull(edmType, "edmType");
 
        return edmType.GetType().Name == "EnumType";
    }
 
    public PrimitiveType GetEnumUnderlyingType(EdmType enumType)
    {
        ArgumentNotNull(enumType, "enumType");
 
        return (PrimitiveType)enumType.GetType().GetProperty("UnderlyingType").GetValue(enumType, null);
    }
 
    public string CreateLiteral(object value)
    {
        if (value == null || value.GetType() != typeof(TimeSpan))
        {
            return _code.CreateLiteral(value);
        }
 
        return string.Format(CultureInfo.InvariantCulture, "new TimeSpan({0})", ((TimeSpan)value).Ticks);
    }
    
    public bool VerifyCaseInsensitiveTypeUniqueness(IEnumerable<string> types, string sourceFile)
    {
        ArgumentNotNull(types, "types");
        ArgumentNotNull(sourceFile, "sourceFile");
        
        var hash = new HashSet<string>(StringComparer.InvariantCultureIgnoreCase);
        if (types.Any(item => !hash.Add(item)))
        {
            _errors.Add(
                new CompilerError(sourceFile, -1, -1, "6023",
                    String.Format(CultureInfo.CurrentCulture, GetResourceString("Template_CaseInsensitiveTypeConflict"))));
            return false;
        }
        return true;
    }
    
    public IEnumerable<SimpleType> GetEnumItemsToGenerate(IEnumerable<GlobalItem> itemCollection)
    {
        return GetItemsToGenerate<SimpleType>(itemCollection)
            .Where(e => IsEnumType(e));
    }
    
    public IEnumerable<T> GetItemsToGenerate<T>(IEnumerable<GlobalItem> itemCollection) where T: EdmType
    {
        return itemCollection
            .OfType<T>()
            .Where(i => !i.MetadataProperties.Any(p => p.Name == ExternalTypeNameAttributeName))
            .OrderBy(i => i.Name);
    }
 
    public IEnumerable<string> GetAllGlobalItems(IEnumerable<GlobalItem> itemCollection)
    {
        return itemCollection
            .Where(i => i is EntityType || i is ComplexType || i is EntityContainer || IsEnumType(i))
            .Select(g => GetGlobalItemName(g));
    }
 
    public string GetGlobalItemName(GlobalItem item)
    {
        if (item is EdmType)
        {
            return ((EdmType)item).Name;
        }
        else
        {
            return ((EntityContainer)item).Name;
        }
    }
 
    public IEnumerable<EdmProperty> GetSimpleProperties(EntityType type)
    {
        return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type);
    }
    
    public IEnumerable<EdmProperty> GetSimpleProperties(ComplexType type)
    {
        return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type);
    }
    
    public IEnumerable<EdmProperty> GetComplexProperties(EntityType type)
    {
        return type.Properties.Where(p => p.TypeUsage.EdmType is ComplexType && p.DeclaringType == type);
    }
    
    public IEnumerable<EdmProperty> GetComplexProperties(ComplexType type)
    {
        return type.Properties.Where(p => p.TypeUsage.EdmType is ComplexType && p.DeclaringType == type);
    }
 
    public IEnumerable<EdmProperty> GetPropertiesWithDefaultValues(EntityType type)
    {
        return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type && p.DefaultValue != null);
    }
    
    public IEnumerable<EdmProperty> GetPropertiesWithDefaultValues(ComplexType type)
    {
        return type.Properties.Where(p => p.TypeUsage.EdmType is SimpleType && p.DeclaringType == type && p.DefaultValue != null);
    }
 
    public IEnumerable<NavigationProperty> GetNavigationProperties(EntityType type)
    {
        return type.NavigationProperties.Where(np => np.DeclaringType == type);
    }
    
    public IEnumerable<NavigationProperty> GetCollectionNavigationProperties(EntityType type)
    {
        return type.NavigationProperties.Where(np => np.DeclaringType == type && np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many);
    }
    
    public FunctionParameter GetReturnParameter(EdmFunction edmFunction)
    {
        ArgumentNotNull(edmFunction, "edmFunction");
 
        var returnParamsProperty = edmFunction.GetType().GetProperty("ReturnParameters");
        return returnParamsProperty == null
            ? edmFunction.ReturnParameter
            : ((IEnumerable<FunctionParameter>)returnParamsProperty.GetValue(edmFunction, null)).FirstOrDefault();
    }
 
    public bool IsComposable(EdmFunction edmFunction)
    {
        ArgumentNotNull(edmFunction, "edmFunction");
 
        var isComposableProperty = edmFunction.GetType().GetProperty("IsComposableAttribute");
        return isComposableProperty != null && (bool)isComposableProperty.GetValue(edmFunction, null);
    }
 
    public IEnumerable<FunctionImportParameter> GetParameters(EdmFunction edmFunction)
    {
        return FunctionImportParameter.Create(edmFunction.Parameters, _code, _ef);
    }
 
    public TypeUsage GetReturnType(EdmFunction edmFunction)
    {
        var returnParam = GetReturnParameter(edmFunction);
        return returnParam == null ? null : _ef.GetElementType(returnParam.TypeUsage);
    }
    
    public bool GenerateMergeOptionFunction(EdmFunction edmFunction, bool includeMergeOption)
    {
        var returnType = GetReturnType(edmFunction);
        return !includeMergeOption && returnType != null && returnType.EdmType.BuiltInTypeKind == BuiltInTypeKind.EntityType;
    }
}
 
public class EdmMetadataLoader
{
    private readonly IDynamicHost _host;
    private readonly System.Collections.IList _errors;
 
    public EdmMetadataLoader(IDynamicHost host, System.Collections.IList errors)
    {
        ArgumentNotNull(host, "host");
        ArgumentNotNull(errors, "errors");
 
        _host = host;
        _errors = errors;
    }
 
    public IEnumerable<GlobalItem> CreateEdmItemCollection(string sourcePath)
    {
        ArgumentNotNull(sourcePath, "sourcePath");
 
        if (!ValidateInputPath(sourcePath))
        {
            return new EdmItemCollection();
        }
 
        var schemaElement = LoadRootElement(_host.ResolvePath(sourcePath));
        if (schemaElement != null)
        {
            using (var reader = schemaElement.CreateReader())
            {
                IList<EdmSchemaError> errors;
                var itemCollection = MetadataItemCollectionFactory.CreateEdmItemCollection(new[] { reader }, out errors);
 
                ProcessErrors(errors, sourcePath);
 
                return itemCollection;
            }
        }
        return new EdmItemCollection();
    }
 
    public string GetModelNamespace(string sourcePath)
    {
        ArgumentNotNull(sourcePath, "sourcePath");
 
        if (!ValidateInputPath(sourcePath))
        {
            return string.Empty;
        }
 
        var model = LoadRootElement(_host.ResolvePath(sourcePath));
        if (model == null)
        {
            return string.Empty;
        }
 
        var attribute = model.Attribute("Namespace");
        return attribute != null ? attribute.Value : "";
    }
 
    private bool ValidateInputPath(string sourcePath)
    {
        if (sourcePath == "$" + "edmxInputFile" + "$")
        {
            _errors.Add(
                new CompilerError(_host.TemplateFile ?? sourcePath, 0, 0, string.Empty,
                    GetResourceString("Template_ReplaceVsItemTemplateToken")));
            return false;
        }
 
        return true;
    }
 
    public XElement LoadRootElement(string sourcePath)
    {
        ArgumentNotNull(sourcePath, "sourcePath");
 
        var root = XElement.Load(sourcePath, LoadOptions.SetBaseUri | LoadOptions.SetLineInfo);
        return root.Elements()
            .Where(e => e.Name.LocalName == "Runtime")
            .Elements()
            .Where(e => e.Name.LocalName == "ConceptualModels")
            .Elements()
            .Where(e => e.Name.LocalName == "Schema")
            .FirstOrDefault()
                ?? root;
    }
 
    private void ProcessErrors(IEnumerable<EdmSchemaError> errors, string sourceFilePath)
    {
        foreach (var error in errors)
        {
            _errors.Add(
                new CompilerError(
                    error.SchemaLocation ?? sourceFilePath,
                    error.Line,
                    error.Column,
                    error.ErrorCode.ToString(CultureInfo.InvariantCulture),
                    error.Message)
                {
                    IsWarning = error.Severity == EdmSchemaErrorSeverity.Warning
                });
        }
    }
    
    public bool IsLazyLoadingEnabled(EntityContainer container)
    {
        string lazyLoadingAttributeValue;
        var lazyLoadingAttributeName = MetadataConstants.EDM_ANNOTATION_09_02 + ":LazyLoadingEnabled";
        bool isLazyLoading;
        return !MetadataTools.TryGetStringMetadataPropertySetting(container, lazyLoadingAttributeName, out lazyLoadingAttributeValue)
            || !bool.TryParse(lazyLoadingAttributeValue, out isLazyLoading)
            || isLazyLoading;
    }
}
 
public static void ArgumentNotNull<T>(T arg, string name) where T : class
{
    if (arg == null)
    {
        throw new ArgumentNullException(name);
    }
}
    
private static readonly Lazy<System.Resources.ResourceManager> ResourceManager =
    new Lazy<System.Resources.ResourceManager>(
        () => new System.Resources.ResourceManager("System.Data.Entity.Design", typeof(MetadataItemCollectionFactory).Assembly), isThreadSafe: true);
 
public static string GetResourceString(string resourceName)
{
    ArgumentNotNull(resourceName, "resourceName");
 
    return ResourceManager.Value.GetString(resourceName, null);
}
 
#>

Entity Framework Function Import does not work with SP which use temp tables in it

If you ever tried to generate Entity Framework model from database for Stored Procedures (Function Import) - you might have notices that Entity Framework cannot receive information about columns for those SP which uses temp tables in it.

The Reason for that is that EF importer tries to execute that SP with FMTONLY set to ON, which makes you SP fail, as temp tables are not created when FMTONLY=ON.

In order to make those SP work, you can make the following changes:
1) Add to the beginning of the SP code:
DECLARE @FMTONLY BIT;

if 1=0 
BEGIN
   set @FMTONLY = 1;
   SET FMTONLY OFF;
END

2) Add to the end of SP:

if @FMTONLY = 1 
BEGIN
   SET FMTONLY ON;
END

This will fix your issue with Function Import caused by using temp tables. If you keep seeing that Entity Framework does not receive information about columns, probably the problem is in something else, you can figure it out trying to execute your store proc with FMTONLY ON, like that:

SET FMTONLY ON;
exec MyProcedureThatDoesNotReturnsColumnsToEF

T4 Template for Visual Studio Settings file

Let's say you have .NET application with settings. Visual Studio does a great job generating class with properties that allows to access those settings, and let's say if you renamed the Setting name you will find out all reference errors during compile time, which is great.

However sometimes you might need to get names of Settings properties as constants - you might need it for using those settings in Attribute initializers, which allow only constant parameters.

So here is T4 template that generates subclass with names as constants and names as Enumerable (for strongly typed references):

<#@ template language="C#" debug="true" hostSpecific="true" #>
<#@ output extension=".cs" #>
<#@ Assembly Name="System.Core.dll" #>
<#@ Assembly Name="System.Xml.dll" #>
<#@ Assembly Name="System.Xml.Linq.dll" #>
<#@ Assembly Name="System.Windows.Forms.dll" #>
<#@ Assembly name="System.Configuration"#>
<#@ assembly name="EnvDTE" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System.Collections" #>
<#@ import namespace="System.Collections.Specialized"#>
<#@ import namespace="System.Collections.Generic" #> 
<#@ import namespace="System.Configuration" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#
 const string SettingsFile = "Settings.settings";
#>
/* 
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a template.
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
 */
using System;
using System.Configuration;
<# 
 
 var doc = XDocument.Load(Path.Combine(this.GetTemplatePath(), SettingsFile));
 var xmlNs = doc.Root.Name.Namespace;
#>
namespace <#= doc.Root.Attribute("GeneratedClassNamespace").Value #>
{
 partial class <#= doc.Root.Attribute("GeneratedClassName").Value #>
 {
        /// <summary>
        /// Contains all settings' names
        /// </summary>
  public class Names
  {
<# foreach(var setting in doc.Descendants(xmlNs+"Setting")) {
  var settingName = setting.Attribute("Name").Value;
#>
<# 
     var settingDescription = setting.Attribute("Description")==null?null:setting.Attribute("Description").Value;
  if(!string.IsNullOrEmpty(settingDescription))
  {
 #>
   /// <summary>
   /// <#= settingDescription #>
   /// </summary>
 <#  } #>
   public const string <#= GetSafeName(settingName) #> = "<#= settingName #>";
<# } #>
  }
 
        /// <summary>
        /// Contains enumeration of all settings, you can use it as strong type key for specific setting
        /// </summary>
  public enum NameEnum
  {
<# foreach(var setting in doc.Descendants(xmlNs+"Setting")) {
    var settingName = setting.Attribute("Name").Value;#>
<# 
     var settingDescription = setting.Attribute("Description")==null?null:setting.Attribute("Description").Value;
  if(!string.IsNullOrEmpty(settingDescription))
  {
 #>
   /// <summary>
   /// <#= settingDescription #>
   /// </summary>
 <#  } #>
   <#= GetSafeName(settingName) #>,
<# } #>
  }
 
        /// <summary>
        /// Gives setting value by name passed as enum
        /// </summary>
        /// <param name="settingName">setting key</param>
        /// <returns>setting value</returns>
  public static object GetValue(NameEnum settingName)
  {
   return Default[settingName.ToString()];
  }
 
        /// <summary>
        /// Gives specified type setting value by name passed as enum
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="settingName"></param>
        /// <returns>setting calue of specified type</returns>
  public static T GetValue<T>(NameEnum settingName)
  {
   return (T)Default[settingName.ToString()];
  }
 }
}
<#+ 
    public string GetTemplatePath ( )
    {
        return Path.GetDirectoryName(Host.TemplateFile);
    }
 
 public string GetSafeName ( string value)
 {
  var builder = new System.Text.StringBuilder();
  
  foreach(var ch in value)
  {
   if (Char.IsLetterOrDigit(ch) || ch == '_')
    builder.Append(ch);
   else if (ch == '.')
    builder.Append('_');   
  };
  
  return builder.ToString();
 }
 #>

You will can use this template for other settings files in your application, in order to do that just change SettingsFile constant.