Posted in: Entity Framework

SQL helper script to generate properties for an Entity Framework model

Posted on October 16, 2013 by Michael Roma

This post shows a T-SQL script that can be used to generate the properties for a class that correspond to to a field in a database table. This can be used with Entity Framework.

T-SQL script to generate the properties:

-- define the table to return back
declare @t nvarchar(100); set @t = 'lookup_site_url'

-- return back the properties
select 	
	'public ' + t + ' ' + n + ' { get; set; }'	
	
from (
	select c.name as n, 

		-- convert t-sql type to .net c# type
		case c.user_type_id
			when 127 then 'long'
			when 167 then 'string'
			when 35 then 'string'
			when 231 then 'string'
			when 241 then 'string'	
			when 56 then 'int'
			when 61 then 'DateTime'
			when 104 then 'bool'
			when 62 then 'double'
			when 60 then 'decimal'
			when 106 then 'decimal'
			when 36 then 'Guid'
			else null
		end as t, user_type_id, max_length	 
	
	from 
		sys.columns c, sys.tables t 
	where 
		c.object_id = t.object_id and t.name = @t
) d

Connecting to a SQL Compact database using Entity Framwork in ASP.NET

Posted on April 3, 2013 by Michael Roma

The following shows how to setup a proper connection string and DbContext in Entity Framework in order to connect to a SQL Compact database in ASP.NET.

Here is an example configuration string in your Web.Config: Here is the code for a base DbContext that uses the above connection string:

public class BaseRepository : DbContext, IDisposable
{
    // constructor, connect to database
    public BaseRepository()
        : base(new SqlCeConnection("myDatabase"),
                 contextOwnsConnection: true) { }        
    
    // dispose
    protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
    }   
} 

Using the LIKE filter in Entity Framework

Posted on March 19, 2013 by Michael Roma

In Entity Framework, there is no LIKE statement, but the PATINDEX function can be used. Consider the following domain:

// Base table entity
public abstract class TableBase
{
    [Key]
    public int id { get; set; }
}


// Customer entity
public class Customer: TableBase
{                    
    // customer fields   
    public string Name { get; set; }        
    public int? Type { get; set; }

    // contacts collection
    public virtual ICollection Contacts { get; set; }
}

// Contact entity
public class Contact : TableBase
{
    // contact fields
    public string FirstName { get; set; }
    public string LastName { get; set; }        
}

// Customer View entity (not mapped)
[NotMapped]
public class CustomerView
{
    // define customer
    public Customer Customer { get; set; }

    // define count of contacts
    public int ContactCount { get; set; }
}

You can use the SqlFunctions.PatIndex function to filter by customers with a B in the name.

var customers = 
    Customers
        .Where(c => SqlFunctions.PatIndex("%b%", c.Name) > 0)
        .ToList();

Using Contains in Entity Framework for WHERE IN clause

Posted on March 16, 2013 by Michael Roma

A common problem with parameterized queries is when you have the need for a WHERE IN clause and the IN list is variable. This is solved with Entity Framework using the Contains methods of the list.

Consider you have the following domain:

// Base table entity
public abstract class TableBase
{
    [Key]
    public int id { get; set; }
}


// Customer entity
public class Customer: TableBase
{                    
    // customer fields   
    public string Name { get; set; }        
    public int? Type { get; set; }

    // contacts collection
    public virtual ICollection Contacts { get; set; }
}

// Contact entity
public class Contact : TableBase
{
    // contact fields
    public string FirstName { get; set; }
    public string LastName { get; set; }        
}

// Customer View entity (not mapped)
[NotMapped]
public class CustomerView
{
    // define customer
    public Customer Customer { get; set; }

    // define count of contacts
    public int ContactCount { get; set; }
}

You can use the following statement to get a filtered list of customers by types:

var typeList = new int[] { 2, 4, 5 };

var customerList = 
    Customers
        .Where(c => typeList.Contains(c.Type))
        .ToList();

Easy way to get distinct values from a .NET List

Posted on November 25, 2012 by Michael Roma

The follow example shows how to get a list of objects that are distinct based on the Name property. The example groups by name, then select the first object in the list of in that grouping and returns to a new list.

// list definition
public class MyListItem
{
    public string Name { get; set; }
    public string Description { get; set; }
}

// example with myList being an instance of List
myList
    .GroupBy(x => x.Name)
    .Select(x => x.First())