Lessons learned from WebAPI and MVC Implementations

My Notes on a painful journey to learn, make and publish a Azure hosted MVC, Sinlge-Page application, Android client app and a WebAPI working with-one another. Software technology can be real pain in the ass!!!

JSON and Self referencing loop

If you get the following error:

Self referencing loop detected for property ‘your model’ with type ‘System.Data.Entity.DynamicProxies

Solution:

Loop Reference handling in Web API

My Solution:

I used [JsonIgnore] attribute to tell the proper inheritance to JSON serialization.

using Newtonsoft.Json;

public partial class Set
{
public Set()
{
this.Exercises = new HashSet<Exercise>();
}

public long Id { get; set; }
public string Name { get; set; }
public string UserId { get; set; }

public virtual ICollection<Exercise> Exercises { get; set; }
}

public partial class ExerciseRecord
{
public long Id { get; set; }
public double Record { get; set; }
public System.DateTime Date { get; set; }
public System.DateTime StartDate { get; set; }
public System.DateTime EndDate { get; set; }
public long ExerciseId { get; set; }

[JsonIgnore]
public virtual Exercise Exercise { get; set; }
}

public partial class Exercise
{
public Exercise()
{
this.ExerciseAttributes = new HashSet<ExerciseAttribute>();
this.ExerciseRecords = new HashSet<ExerciseRecord>();
}

public long Id { get; set; }
public string Name { get; set; }
public double Target { get; set; }
public long SetId { get; set; }
[JsonIgnore]
public virtual Set Set { get; set; }
public virtual ICollection<ExerciseAttribute> ExerciseAttributes { get; set; }
public virtual ICollection<ExerciseRecord> ExerciseRecords { get; set; }
}

 

MVC loads older script files

This is due to browser script caching. The easiest solution for this is to set the browser which you are using to debug to retrieve the newest versions of web page content on each time you visit a webpage.

Missing Key definition from Model when creating a controller

You might get an error like this: EntityType ‘your type’ has no key defined. Define the key for this EntityType.

To fix such problems simply add this namespace definitions:

using System.ComponentModel.DataAnnotations;

And then define in your data model a key like this:

public class ColumnDataHighChart
{
[Key]
public int ID { get; set; }
public String Title { get; set; }
public String SubTitle { get; set; }
public IList<String> xAxisCategories { get; set; }
public String yAxisTitle { get; set; }
public IList<SeriesDataHighChart> Series { get; set; }

}

 

Intercepting web requests

 

I have two ways of doing this:

  1. A delegation handler
  2. Or an action filter for a controller.

Delegation handler

DelegatingHandler Class

Sample code:

public class AuthHandler : DelegatingHandler
{
protected async override Task<HttpResponseMessage> SendAsync(
HttpRequestMessage request, CancellationToken cancellationToken)
{
// Call the inner handler.
var response = await base.SendAsync(request, cancellationToken);
return response;
}
}

Action filter for a controlle

Notice that for MVC and Web API there are two different sets of action filter definitions:

http://msdn.microsoft.com/en-us/library/system.web.http.filters.actionfilterattribute(v=vs.118).aspx

http://msdn.microsoft.com/en-us/library/system.web.mvc.actionfilterattribute(v=vs.118).aspx

MVC Sample:

public class AuthenticationActionFilterHelper : ActionFilterAttribute
{
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
base.OnActionExecuting(filterContext);
if (HttpContext.Current != null && HttpContext.Current.User != null && HttpContext.Current.User.Identity.IsAuthenticated)
{
if (SessionHelper.LoggedInUser<AspNetUser>(HttpContext.Current.User.Identity.Name) == null)
{
//SessionHelper.UserSessionID = user.Id;
AspNetUsersController aspUserCon = new AspNetUsersController();
var sessionUser = aspUserCon.GetUser(HttpContext.Current.User.Identity.Name);
//SessionHelper.UserSessionID = user.UserName;
SessionHelper.LoggedInUser<AspNetUser>(sessionUser, sessionUser.UserName);
}
}
}

}

WebAPI Sample:

public class AuthenticationActionFilterHelper : ActionFilterAttribute
{
public override void OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
{
base.OnActionExecuted(actionExecutedContext);
}

public override System.Threading.Tasks.Task OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, System.Threading.CancellationToken cancellationToken)
{
return base.OnActionExecutedAsync(actionExecutedContext, cancellationToken);
}

public override void OnActionExecuting(HttpActionContext actionContext)
{
base.OnActionExecuting(actionContext);
if (HttpContext.Current != null && HttpContext.Current.User != null && HttpContext.Current.User.Identity.IsAuthenticated)
{
if (SessionHelper.LoggedInUser<AspNetUser>(HttpContext.Current.User.Identity.Name) == null)
{
//SessionHelper.UserSessionID = user.Id;
AspNetUsersController aspUserCon = new AspNetUsersController();
var sessionUser = aspUserCon.GetUser(HttpContext.Current.User.Identity.Name);
//SessionHelper.UserSessionID = user.UserName;
SessionHelper.LoggedInUser<AspNetUser>(sessionUser, sessionUser.UserName);
}
}
}

public override System.Threading.Tasks.Task OnActionExecutingAsync(HttpActionContext actionContext, System.Threading.CancellationToken cancellationToken)
{
return base.OnActionExecutingAsync(actionContext, cancellationToken);
}
}

 

Lambda Expression “Magic” 🙂

Retrieve distinct parent from child elements

As the title suggests I needed to retrieve the parent from a multilevel data set:

 

First I needed to get the child elements and in this exmaple it is assumed that you have the child elements retrieved.

Once you have the child elements it is time to get the distinct parent elements. for this I needed a way to group the distinct parent from the child elements. Here are my steps:

  • Get child elements:

ExerciseRecordsController exerciseRecordsController = new ExerciseRecordsController();
var exerciseRecordsData = exerciseRecordsController.GetExerciseRecords().Where(er => er.Date > startDate && er.Date < endDate && er.Exercise.Set.UserId.ToLower().CompareTo(this.user.Id.ToLower()) == 0);

  • Define a custom extension named “DistinctBy”

public static class LambdaExtensions
{
public static IEnumerable<t> DistinctBy<t>(this IEnumerable<t> list, Func<t, object> propertySelector)
{
return list.GroupBy(propertySelector).Select(x => x.First());
}
} Original code from: http://www.elevenwinds.com/linq-distinctby-with-lambda-expression-parameter

  • Apply the new extension on the data set

var setsData = exerciseRecordsData.DistinctBy(o => o.Exercise.SetId).Select( o => o.Exercise.Set);

How to create/populate a collection with data with an unknown data type

You may ask yourself why would anyone needs this? Well I do not why would others needs this but I came into a situation where I needed this.

I had a solution where I needed to be able to create data from a back-end server WebAPI to a JS HighCharts JS library without knowing what kind of data I would be processing, also I wanted to have the possibility to extend the back-end code so that it can return any kind of data to the client and let the client figure out what to do with the data.

So how to do this?

In a human language it goes something like this: Use LINQ in your code to go through the data set, select your data and return it as and array of objects, then create a new collection by passing to the constructor your processed data as an array of objects. Ofcourse your collection must store objects as well. The data type information is going to be stored because every class in C# is a descendant of the Object class.

public class SeriesDataHighChart
{
[Key]
public long ID { get; set; }
public String Name { get; set; }
public IList<object> Data { get; set; }
}

 

// One series corresponds to one set and data for each month
SeriesDataHighChart seriesData = new SeriesDataHighChart();

seriesData.ID = chartSet.Id;
seriesData.Name = chartSet.Name;
var seriesMonthsActivityCountData = (from monthActivityCount in chartSet.ChartSetMonthsData
select new object[] { monthActivityCount.ActivityCount as object });
seriesData.Data = new List<object>(seriesMonthsActivityCountData.ToArray());

hsData.Series.Add(seriesData);

 

You could also return an array of more complex object such as a key value pairs:

var sd = from d in unparsedData
select new object[] { d.Key as object, d.Value as object };

newSeries.Data = new Data(sd.ToArray());

Avoiding “Sequence contains no elements” exception in object initializers

If you have something like this in your code:

chartExercise.ChatMonthsData.Add(new ChartExerciseMonthData
{
ActivityCount = exercise.ExerciseRecords.Where(m => m.Date.Month == month && m.Date >= startDate && m.Date <= endDate).Count(),
StartDate = DateTime.Now.StartOfMonth(month),
EndDate = DateTime.Now.EndOfMonth(month),
MonthRecordAverage = exercise.ExerciseRecords.Where(m => m.Date.Month == month && m.Date >= startDate && m.Date <= endDate).Average(a => a.Record)

});

 

The Average lambda expression will throw the above exception error message because the Where clause may return Zero elements back(Notice that for example the Count expression will not throw a similar exception).

To fix(go around the problem, yes there might be other solutions but this was mine at the moment 🙂 ) I created an anonymous function that checks if there are elements returned by the clause and only then perform the Average operation on the elements. The solution is highlighted with the green color.

chartExercise.ChatMonthsData.Add(new ChartExerciseMonthData
{
ActivityCount = exercise.ExerciseRecords.Where(m => m.Date.Month == month && m.Date >= startDate && m.Date <= endDate).Count(),
StartDate = DateTime.Now.StartOfMonth(month),
EndDate = DateTime.Now.EndOfMonth(month),
MonthRecordAverage = new Func<double>(() => {
double averageRecord = 0;
var exerciseRecordByDateRange = exercise.ExerciseRecords.Where(m => m.Date.Month == month && m.Date >= startDate && m.Date <= endDate);
if (exerciseRecordByDateRange.Count() > 0)
averageRecord = exerciseRecordByDateRange.Average(a => a.Record);

return (averageRecord);
})()
});

 Get the count for a complex data structure/hierarchy, tree like

A rather simple implementation, choose to retrieve any records inside your main records set with a where:

set.Exercises.Where(o => o.ExerciseRecords.Any(m => m.Date.Month == month)).Count()

Update Azure SQL Database via SQL Server management studio and Generated scripts

  1. Mouse second button on database > Taskas > Generate Scripts > Choose your objects (Chose objects view) > Select “Advanced” button, then in the “Script for the database engine type” select option Windows Azure SQL Database
  2. Next open the database connection with management studio to your Azure SQL Database.
  3. Create a new empty database
  4. Open a new query windows and simply add the generated script to this window and run the script against the new empty database. This will create the structure and data if you selected so.

After deploying your WebAPI you get a following error when accessing your database data “There is already an open DataReader associated with this Command which must be closed first.”

To fix this error simply add the following to your connection strings used in your web api in Azure MultipleActiveResultSets=true.

Enable WebAPI Cross-Origin Resource Sharing (CORS)

You might run into problems after deploying your WebAPI to Azure and trying to access your api from different origins. Here is a solution:

http://www.codeproject.com/Articles/742532/Using-Web-API-Individual-User-Account-plus-CORS-En

Summary of the article above: Install the following nuget package in your WebAPI project: Microsoft.AspNet.WebApi.Cors

In your webapiconfig add the following(in green):

public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
// Configure Web API to use only bearer token authentication.
config.SuppressDefaultHostAuthentication();
config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType));

var cors = new EnableCorsAttribute(“*”, “*”, “*”);
config.EnableCors(cors);

// Web API routes
config.MapHttpAttributeRoutes();

config.Routes.MapHttpRoute(
name: “DefaultApi”,
routeTemplate: “api/{controller}/{id}”,
defaults: new { id = RouteParameter.Optional }
);

config.MessageHandlers.Add(new Handler.AuthHandler());
}
}

Next add the following to your ApplicationOAuthProvider.GrantResourceOwnerCredentials function:

public override async Task GrantResourceOwnerCredentials(OAuthGrantResourceOwnerCredentialsContext context)
{
context.OwinContext.Response.Headers.Add(“Access-Control-Allow-Origin”, new[] { “*” });
var userManager = context.OwinContext.GetUserManager<ApplicationUserManager>();

ApplicationUser user = await userManager.FindAsync(context.UserName, context.Password);
//SessionHelper.UserSessionID = user.Id;
AspNetUsersController aspUserCon = new AspNetUsersController();
var sessionUser = aspUserCon.GetUser(user.Id);
//SessionHelper.UserSessionID = user.UserName;
SessionHelper.LoggedInUser<AspNetUser>(sessionUser, user.UserName);
if (user == null)
{
context.SetError(“invalid_grant”, “The user name or password is incorrect.”);
return;
}

ClaimsIdentity oAuthIdentity = await user.GenerateUserIdentityAsync(userManager,
OAuthDefaults.AuthenticationType);
ClaimsIdentity cookiesIdentity = await user.GenerateUserIdentityAsync(userManager,
CookieAuthenticationDefaults.AuthenticationType);

AuthenticationProperties properties = CreateProperties(user.UserName);
AuthenticationTicket ticket = new AuthenticationTicket(oAuthIdentity, properties);
context.Validated(ticket);
context.Request.Context.Authentication.SignIn(cookiesIdentity);

}

Notice that these changes may have undesired effects. Find out if these are suitable to your needs.

Good to know!?: .NET – Accessing, Querying and Manipulating data with Entity Framework

Hi,

I gathered some links and resources on data manipulation with the .NET Framework. Hope this helps and works as a reference card what is available:

ADO.NET http://msdn.microsoft.com/en-us/library/e80y5yhx(v=vs.110).aspx
Entity Framework http://msdn.microsoft.com/en-US/data/ef
Configuring Parameters and Parameter Data Types http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx
.NET Framework Data Providers http://msdn.microsoft.com/en-us/library/a6cd7c08(v=vs.110).aspx
DataSet Class http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx
Retrieving Data Using a DataReader http://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx
Entity Framework – Database First http://msdn.microsoft.com/en-us/data/jj206878.aspx
Entity Framework- Code First to a New Database http://msdn.microsoft.com/en-us/data/jj193542.aspx
ADO.NET Entity Data Model Designer http://msdn.microsoft.com/en-us/library/vstudio/cc716685(v=vs.100).aspx
Entity Data Model Wizard http://msdn.microsoft.com/en-us/library/vstudio/bb399247(v=vs.100).aspx
Create Database Wizard (Master Data Services Configuration Manager) http://technet.microsoft.com/en-us/library/ee633799.aspx
Update Model Wizard (Entity Data Model Tools) http://msdn.microsoft.com/en-us/library/vstudio/cc716705(v=vs.100).aspx
Using the DbContext API http://msdn.microsoft.com/en-us/data/gg192989.aspx
Table-per-Type vs Table-per-Hierarchy Inheritance http://blog.devart.com/table-per-type-vs-table-per-hierarchy-inheritance.html
Relational database management system http://en.wikipedia.org/wiki/Relational_database_management_system
ObjectContext Class http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext(v=vs.110).aspx
DBContext vs ObjectContexts http://www.entityframeworktutorial.net/EntityFramework4.3/dbcontext-vs-objectcontext.aspx
DbContext Class http://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext(v=vs.113).aspx
ObjectContext management http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.contextoptions(v=vs.110).aspx
How to: Use Lazy Loading to Load Related Objects http://msdn.microsoft.com/en-us/library/vstudio/dd456846(v=vs.100).aspx
EntityObject Class http://msdn.microsoft.com/en-us/library/system.data.objects.dataclasses.entityobject(v=vs.110).aspx
EdmEntityTypeAttribute Class http://msdn.microsoft.com/en-us/library/system.data.objects.dataclasses.edmentitytypeattribute(v=vs.110).aspx
SerializableAttribute Class http://msdn.microsoft.com/en-us/library/system.serializableattribute.aspx
DataContractAttribute Class http://msdn.microsoft.com/en-us/library/system.runtime.serialization.datacontractattribute.aspx
Entity Framework (EF) Documentation http://msdn.microsoft.com/en-us/data/ee712907.aspx
OData protocol http://www.odata.org/
Open Data Protocol by Example http://msdn.microsoft.com/en-us/library/ff478141.aspx
WCF Data Services Overview http://msdn.microsoft.com/en-us/library/cc668794(v=vs.110).aspx
Using the REST Interface http://msdn.microsoft.com/en-us/library/ff798339.aspx
Understanding Service-Oriented Architecture http://msdn.microsoft.com/en-us/library/aa480021.aspx
WCF Data Services 4.5 http://msdn.microsoft.com/en-us/library/cc668792(v=vs.110).aspx
Advanced using OData in .NET: WCF Data Services http://www.codeproject.com/Articles/135490/Advanced-using-OData-in-NET-WCF-Data-Services
ObjectCache Class http://msdn.microsoft.com/en-us/library/vstudio/system.runtime.caching.objectcache
HttpContext.Cache Property http://msdn.microsoft.com/en-us/library/system.web.httpcontext.cache(v=vs.110).aspx
ASP.NET Application State Overview http://msdn.microsoft.com/en-us/library/ms178594.aspx
ASP.NET Session State Overview http://msdn.microsoft.com/en-us/library/ms178581.aspx
Understanding ASP.NET View State http://msdn.microsoft.com/en-us/library/ms972976.aspx
CacheItemPolicy Class http://msdn.microsoft.com/en-us/library/system.runtime.caching.cacheitempolicy(v=vs.110).aspx
CacheItemPriority Enumeration http://msdn.microsoft.com/en-us/library/system.web.caching.cacheitempriority.aspx
ChangeMonitor Class http://msdn.microsoft.com/en-us/library/system.runtime.caching.changemonitor(v=vs.110).aspx
CacheDependency Class http://msdn.microsoft.com/en-us/library/system.web.caching.cachedependency.aspx
System.Transactions Namespace http://msdn.microsoft.com/en-us/library/system.transactions.aspx
EntityTransaction Class http://msdn.microsoft.com/en-us/library/system.data.entityclient.entitytransaction.aspx
EntityCommand Class http://msdn.microsoft.com/en-us/library/system.data.entityclient.entitycommand.aspx
EntityConnection Class http://msdn.microsoft.com/en-us/library/system.data.entityclient.entityconnection(v=vs.110).aspx
SqlTransaction Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx
System.Data.EntityClient Namespace http://msdn.microsoft.com/en-us/library/system.data.entityclient(v=vs.110).aspx
IsolationLevel Enumeration http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
TransactionScope Class http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
System.Xml Namespaces http://msdn.microsoft.com/en-us/library/gg145036(v=vs.110).aspx
XmlWriter Class http://msdn.microsoft.com/en-us/library/system.xml.xmlwriter.aspx
XML Documents and Data http://msdn.microsoft.com/en-us/library/2bcctyt8(v=vs.110).aspx
XmlDocument Class http://msdn.microsoft.com/en-us/library/system.xml.xmldocument.aspx
XmlReader Class http://msdn.microsoft.com/en-us/library/vstudio/system.xml.xmlreader
XPath Examples http://msdn.microsoft.com/en-us/library/ms256086(v=vs.110).aspx
LINQ to XML [from BPUEDev11] http://msdn.microsoft.com/en-us/library/bb387098.aspx
LINQ to XML Overview http://msdn.microsoft.com/en-us/library/bb387061.aspx
XElement Class http://msdn.microsoft.com/en-us/library/system.xml.linq.xelement.aspx
LINQ (Language-Integrated Query) http://msdn.microsoft.com/en-us/library/bb397926.aspx
DbContext.SaveChanges Method http://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext.savechanges(v=vs.113).aspx
DbContext.Set<TEntity> Method http://msdn.microsoft.com/en-us/library/gg696521(v=vs.113).aspx
Object-relational impedance mismatch http://en.wikipedia.org/wiki/Object-Relational_impedance_mismatch
Loading Related Entities (Eager Loading,  Lazy Loading, Explicitly Loading) http://msdn.microsoft.com/en-us/data/jj574232.aspx
Demystifying Entity Framework Strategies: Loading Related Data (Eager Loading,  Lazy Loading, Explicitly Loading) http://msdn.microsoft.com/en-us/magazine/hh205756.aspx
Precompiling LINQ Queries http://msdn.microsoft.com/en-us/magazine/ee336024.aspx
Entity Framework 5: Controlling automatic query compilation http://blogs.msdn.com/b/stuartleeks/archive/2012/06/12/entity-framework-5-controlling-automatic-query-compilation.aspx
Improve Performance with Entity Framework 5 http://devproconnections.com/entity-framework/improve-performance-entity-framework-5
Queries in LINQ to Entities http://msdn.microsoft.com/en-us/library/vstudio/bb399367(v=vs.100).aspx
LINQ to Entities: Basic Concepts and Features http://www.codeproject.com/Articles/246861/LINQ-to-Entities-Basic-Concepts-and-Features
LINQ to Objects http://msdn.microsoft.com/en-us/library/bb397919.aspx
SqlConnectionStringBuilder Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx
ObjectQuery<T> Class http://msdn.microsoft.com/en-us/library/bb345303(v=vs.110).aspx
ObjectQuery Class http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery(v=vs.110).aspx
ObjectQuery.ToTraceString Method http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring(v=vs.110).aspx
System.Data.SqlClient Namespace http://msdn.microsoft.com/en-us/library/System.Data.SqlClient(v=vs.110).aspx
SqlConnection Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
SQL Server Connection Pooling (ADO.NET) http://msdn.microsoft.com/en-us/library/vstudio/8xx3tyca%28v%3Dvs.100%29
DataTable Class http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx
DataSet Class http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx
DataAdapter Class http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.aspx
SqlCommand Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx
SqlCommand.CommandText Property http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext(v=vs.110).aspx
CommandType Enumeration http://msdn.microsoft.com/en-us/library/system.data.commandtype(v=vs.110).aspx
SqlDataAdapter Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx
SqlCommand.ExecuteScalar Method http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx
SqlCommand.ExecuteReader Method http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executereader(v=vs.110).aspx
SqlDataReader Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
SqlDataReader.Read Method http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read.aspx
DbDataAdapter.Fill Method (DataSet) http://msdn.microsoft.com/en-us/library/zxkb3c3d(v=vs.110).aspx
DbDataAdapter.Update Method (DataSet) http://msdn.microsoft.com/en-us/library/at8a576f(v=vs.110).aspx
DataAdapter.AcceptChangesDuringFill Property http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill(v=vs.110).aspx
Working with Datasets in Visual Studio http://msdn.microsoft.com/en-us/library/8bw9ksd6%28v%3Dvs.110%29.aspx
SqlParameter Class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx
EF Designer TPT Inheritance http://msdn.microsoft.com/en-us/data/jj618293.aspx
Walkthrough: Mapping Table-per-Hierarchy Inheritance in Dynamic Data http://msdn.microsoft.com/en-us/library/dd793152.ASPX
Code First to an Existing Database http://msdn.microsoft.com/en-us/data/jj200620.aspx
Model-First in the Entity Framework 4 http://msdn.microsoft.com/en-us/data/ff830362.aspx
ADO.NET Entity Data Model Designer http://msdn.microsoft.com/en-us/library/vstudio/cc716685(v=vs.100).aspx
The ADO.NET Entity Framework Overview http://msdn.microsoft.com/en-us/library/aa697427(v=vs.80).aspx
ADO.NET Entity Data Model Tools http://msdn.microsoft.com/en-us/library/vstudio/bb399249(v=vs.100).aspx
Plain Old CLR Object(POCO) http://en.wikipedia.org/wiki/Plain_Old_CLR_Object
Working with POCO Entities http://msdn.microsoft.com/en-us/library/vstudio/dd456853(v=vs.100).aspx

Good to know: SQL Server 2012 – Part 1

Hi,

It’s been awhile since my last post but here comes my next one on SQL Server. I been gathering SQL Server related links that might come in handy on the most important topics( that I know 🙂 ). Anyway here are the links. Hope it helps some one, enjoy :).

ALTER INDEX (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188388.aspx
ALTER TABLE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190273.aspx
ALTER VIEW (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173846.aspx
binary and varbinary (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188362.aspx
Built-in Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms174318%28v%3DSQL.110%29.aspx
Clustered and Nonclustered Indexes Described http://technet.microsoft.com/en-us/library/ms190457.aspx
COLUMNS_UPDATED (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186329.aspx
Contained Databases http://msdn.microsoft.com/en-us/library/ff929071.aspx
CONTAINS (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187787.aspx
Create Foreign Key Relationships http://msdn.microsoft.com/en-us/library/ms189049.aspx
CREATE FUNCTION (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186755.aspx
CREATE INDEX (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188783.aspx
Create Indexed Views http://msdn.microsoft.com/en-us/library/ms191432.aspx
CREATE PROCEDURE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187926.aspx
CREATE SEQUENCE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ff878091.aspx
CREATE SYNONYM (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177544.aspx
CREATE TABLE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms174979.aspx
CREATE VIEW (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187956.aspx
CREATE XML SCHEMA COLLECTION (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms176009.aspx
Data Compression http://msdn.microsoft.com/en-us/library/cc280449.aspx
Data Type Precedence (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190309.aspx
Data Types (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187752(v=SQL.110).aspx
Database Engine Error Severities http://msdn.microsoft.com/en-us/library/ms164086.aspx
Date and Time Data Types and Functions http://msdn.microsoft.com/en-us/library/ms186724%28v%3DSQL.110%29.aspx
Date and Time Data Types and Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186724
Date and Time Types http://msdn.microsoft.com/en-us/library/ff848733.aspx
DATEADD (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186819.aspx
datetimeoffset (Transact-SQL) http://msdn.microsoft.com/en-us/library/bb630289.aspx
DECLARE CURSOR (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms180169.aspx
Detecting and Ending Deadlocks http://msdn.microsoft.com/en-us/library/ms178104%28SQL.105%29.aspx
Deterministic and Nondeterministic Functions http://msdn.microsoft.com/en-us/library/ms178091.aspx
Difference Between Union vs. Union All – Optimal Performance Comparison http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
Diving into T-SQL Grouping Sets http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/
Dynamic Management Views and Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188754.aspx
ENCRYPTBYCERT (Transact-SQL) http://technet.microsoft.com/en-us/library/ms188061.aspx
ENCRYPTBYPASSPHRASE (Transact-SQL) http://technet.microsoft.com/en-us/library/ms190357.aspx
EOMONTH (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh213020.aspx
Example @@ERROR (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188790.aspx
Example @@TRANCOUNT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187967.aspx
Example: Retrieving Product Model Information as XML http://msdn.microsoft.com/en-us/library/bb510464.aspx
EXCEPT and INTERSECT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188055.aspx
EXECUTE AS Clause (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188354.aspx
Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/
Features Supported by the Editions of SQL Server 2012 http://msdn.microsoft.com/en-us/library/cc645993(SQL.110).aspx
FileTables (SQL Server) http://msdn.microsoft.com/en-us/library/ff929144.aspx
FOR XML (SQL Server) http://msdn.microsoft.com/en-us/library/ms178107.aspx
FORMAT (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh213505.aspx
Formatting Types http://msdn.microsoft.com/en-us/library/26etazsy.aspx
FROM (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177634.aspx
Graphical Execution Plan Icons (SQL Server Management Studio) http://msdn.microsoft.com/en-us/library/ms175913%28v%3DSQL.105%29.aspx
GROUP BY (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177673.aspx
Hardware and Software Requirements for Installing SQL Server 2012 http://msdn.microsoft.com/en-us/library/ms143506
IN (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177682.aspx
Join Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173815.aspx
LAG (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh231256.aspx
LEAD (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh213125.aspx
LIKE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms179859
Locking Hints http://technet.microsoft.com/en-us/library/ms189857.aspx
MERGE (Transact-SQL) http://technet.microsoft.com/en-us/library/bb510625.aspx
Microsoft Office 2010 Filter Packs http://www.microsoft.com/en-us/download/details.aspx?id=17062
money and smallmoney (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms179882.aspx
optimize for ad hoc workloads Server Configuration Option http://msdn.microsoft.com/en-us/library/cc645587.aspx
OUTPUT Clause (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177564.aspx
Page Compression Implementation http://msdn.microsoft.com/en-us/library/cc280464.aspx
PATINDEX (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188395
Plan Caching in SQL Server 2008 http://msdn.microsoft.com/en-us/library/ee343986%28SQL.100%29.aspx
Query Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms181714.aspx
Ranking Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms189798.aspx
Row Compression Implementation http://msdn.microsoft.com/en-us/library/cc280576.aspx
Search Condition (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173545.aspx
Semantic Search (SQL Server) http://technet.microsoft.com/en-us/library/gg492075.aspx
SET ARITHABORT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190306.aspx
SET IMPLICIT_TRANSACTIONS (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187807.aspx
SET TRANSACTION ISOLATION LEVEL (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173763.aspx
SET XACT_ABORT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188792.aspx
Showplan Logical and Physical Operators Reference http://msdn.microsoft.com/en-us/library/ms191158.aspx
sp_estimate_data_compression_savings (Transact-SQL) http://msdn.microsoft.com/en-us/library/cc280574.aspx
sp_sequence_get_range http://msdn.microsoft.com/en-us/library/ff878352.aspx
Specify Computed Columns in a Table http://technet.microsoft.com/en-us/library/ms188300.aspx
SQL Injection http://msdn.microsoft.com/en-us/library/ms161953%28SQL.105%29.aspx
SQL Server Encryption http://technet.microsoft.com/en-us/library/bb510663.aspx
String Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms181984
sys.dm_tran_database_transactions http://msdn.microsoft.com/en-us/library/ms186957(v=sql.90).aspx
Table Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187373.aspx
Table Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187373.aspx
The Data Loading Performance Guide http://msdn.microsoft.com/en-us/library/dd425070.aspx
Troubleshooting Poor Query Performance: Cardinality Estimation http://technet.microsoft.com/en-us/library/ms181034.aspx
TRY_PARSE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188792.aspx
Unicode Compression Implementation http://msdn.microsoft.com/en-us/library/ee240835.aspx
UNION (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms180026.aspx
UPDATE STATISTICS (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187348.aspx
Use AUTO Mode with FOR XML http://msdn.microsoft.com/en-us/library/ms188273.aspx
Use EXPLICIT Mode with FOR XML http://msdn.microsoft.com/en-us/library/ms189068.aspx
Using @@ERROR http://msdn.microsoft.com/en-us/library/ms190193.aspx
Using APPLY http://msdn.microsoft.com/en-us/library/ms175156.aspx
Using Partitioned Views http://msdn.microsoft.com/en-us/library/ms190019.aspx
WHERE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188047.aspx
Views http://msdn.microsoft.com/en-us/library/ms190174.aspx
Write-Ahead Transaction Log http://msdn.microsoft.com/en-us/library/ms186259%28SQL.105%29.aspx
XACT_STATE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms189797.aspx
XQuery Functions against the xml Data Type http://msdn.microsoft.com/en-us/library/ms189254.aspx

Tips: SQL Server – Constraints and Functions

If you are planning to make a table constraint that is more complex than a simple check against a field or two then I recommend using functions. By creating a function you can make complex logic by which you can return True Or False states that indicate to a constraint if the data is what you need it to be. You can also add parameters to a function giving you the dynamic possibility to process data being processed.

Example:

Lets say that you have function that wants to check if a ID value is a certain numeric ID and then you want to make sure that a row being inserted does not have a NULL value in a certain field.

Here is some sample code:

CREATE FUNCTION [dbo].[CheckForNullByID]

(

   — Add the parameters for the function here

   @someID int,

   @someFieldNotNULL char(11)

)

RETURNS bit

AS

BEGIN

   DECLARE @retValue bit = 0

   IF @someID = 40

   BEGIN

          IF @someFieldNotNULL IS NOT NULL

          BEGIN

                 SET @retValue = 1

          END

          ELSE

                 SET @retValue = 0

   END

   ELSE

          SET @retValue = 1

   RETURN @retValue

END

GO

Then you call the function in your constraint like the following expression:

([dbo].[CheckForNullByID]([rowIDValue],[someFieldValueThatIsNotToBeNULL])>(0))

Thats it :)!!!

Hope this helps 🙂