Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

June 2010 - Posts

Dynamic LINQ Methods

Continuing the Dynamic LINQ series, here are some hopefuly useful methods for performing LINQ queries in IQueryable or IQueryable<T> object, with String parameters. The available methods, so far, are:

  • GroupBy
  • OrderBy
  • Skip
  • Take
  • WhereEquals
  • WhereNotEquals

	public static class QueryableExtensions
	{
		public static IQueryable<T&> WhereNotEquals<T&>(this IQueryable<T&> query, String propertyName, Object value)
		{
			return (WhereNotEquals(query as IQueryable, propertyName, value) as IQueryable<T&>);
		}

		public static IQueryable WhereNotEquals(this IQueryable query, String propertyName, Object value)
		{
			Type propertyType = query.GetType().GetGenericArguments() [ 0 ];
			MethodInfo whereMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "Where").ToArray() [ 0 ].MakeGenericMethod(propertyType);

			ParameterExpression parameter = Expression.Parameter
			(
				propertyType,
				"m"
			);

			MemberExpression member = Expression.MakeMemberAccess
			(
				parameter,
				propertyType.GetProperty(propertyName)
			);

			BinaryExpression equal = ParameterExpression.NotEqual
			(
				member,
				(value != null) ? Expression.Constant(value, value.GetType()) : null
			);

			LambdaExpression lambda = Expression.Lambda
			(
				typeof(Func<,>).MakeGenericType(propertyType, typeof(Boolean)),
				equal,
				member.Expression as ParameterExpression
			);

			query = whereMethod.Invoke(null, new Object [] { query, lambda }) as IQueryable;

			return (query);
		}

		public static IQueryable<T&> WhereEquals<T&>(this IQueryable<T&> query, String propertyName, Object value)
		{
			return (WhereEquals(query as IQueryable, propertyName, value) as IQueryable<T&>);
		}

		public static IQueryable WhereEquals(this IQueryable query, String propertyName, Object value)
		{
			Type propertyType = query.GetType().GetGenericArguments() [ 0 ];
			MethodInfo whereMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "Where").ToArray() [ 0 ].MakeGenericMethod(propertyType);

			ParameterExpression parameter = Expression.Parameter
			(
				propertyType,
				"m"
			);

			MemberExpression member = Expression.MakeMemberAccess
			(
				parameter,
				propertyType.GetProperty(propertyName)
			);

			BinaryExpression equal = ParameterExpression.Equal
			(
				member,
				(value != null) ? Expression.Constant(value, value.GetType()) : null
			);

			LambdaExpression lambda = Expression.Lambda(typeof(Func<,>).MakeGenericType(propertyType, typeof(Boolean)), equal, member.Expression as ParameterExpression);

			query = whereMethod.Invoke(null, new Object[]{ query, lambda }) as IQueryable;

			return(query);
		}

		public static IQueryable<T&> GroupBy<T&>(this IQueryable<T&> query, String propertyName)
		{
			return (GroupBy(query as IQueryable, propertyName) as IQueryable<T&>);
		}

		public static IQueryable GroupBy(this IQueryable query, String propertyName)
		{
			Type propertyType = query.GetType().GetGenericArguments() [ 0 ];
			PropertyInfo property = propertyType.GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public);
			MethodInfo groupByMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "GroupBy" && m.GetParameters().Length == 2).ToArray() [ 0 ].MakeGenericMethod(propertyType, property.PropertyType);

			ParameterExpression parameter = Expression.Parameter
			(
				propertyType,
				"m"
			);

			MemberExpression member = Expression.MakeMemberAccess
			(
				parameter,
				propertyType.GetProperty(propertyName)
			);
	
			LambdaExpression lambda = Expression.Lambda
			(
				typeof(Func<,>).MakeGenericType(propertyType, property.PropertyType),
				member,
				member.Expression as ParameterExpression
			);

			query = groupByMethod.Invoke(null, new Object [] { query, lambda }) as IQueryable;

			return (query);
		}

		public static IQueryable<T&> OrderBy<T&>(this IQueryable<T&> query, params String [] properties)
		{
			return (OrderBy(query as IQueryable, properties) as IQueryable<T&>);
		}

		public static IQueryable OrderBy(this IQueryable query, params String [] properties)
		{
			properties = (properties == null) ? new String [ 0 ] : properties.Distinct().ToArray();

			Type propertyType = query.GetType().GetGenericArguments()[ 0 ];
			MethodInfo orderByMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "OrderBy").ToArray() [ 0 ];
			MethodInfo orderByDescMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "OrderByDescending").ToArray() [ 0 ];
			MethodInfo orderThenByMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "ThenBy").ToArray() [ 0 ];
			MethodInfo orderThenByDescMethod = typeof(Queryable).GetMethods(BindingFlags.Public | BindingFlags.Static).Where(m => m.Name == "ThenByDescending").ToArray() [ 0 ];
			String [] parts = null;
			MethodInfo method = null;
			PropertyInfo property = null;
			MemberExpression member = null;
			LambdaExpression orderBy = null;

			for (Int32 i = 0; i < properties.Length; ++i)
			{
				parts = properties[ i ].Split(' ');

				property = propertyType.GetProperty(parts[ 0 ], BindingFlags.Instance | BindingFlags.Public);

				if ((parts.Length == 1) || (parts [ 1 ].Equals("asc", StringComparison.OrdinalIgnoreCase) == true))
				{
					if (i == 0)
					{
						method = orderByMethod.MakeGenericMethod(propertyType, property.PropertyType);
					}
					else
					{
						method = orderThenByMethod.MakeGenericMethod(propertyType, property.PropertyType);
					}
				}
				else if (parts[ 1 ].Equals("desc", StringComparison.OrdinalIgnoreCase) == true)
				{
					if (i == 0)
					{
						method = orderByDescMethod.MakeGenericMethod(propertyType, property.PropertyType);
					}
					else
					{
						method = orderThenByDescMethod.MakeGenericMethod(propertyType, property.PropertyType);
					}
				}

				member = Expression.MakeMemberAccess
				(
					Expression.Parameter(propertyType, "n"),
					property
				);

				orderBy = Expression.Lambda
				(
					member,
					member.Expression as ParameterExpression
				);

				query = method.Invoke(null, new Object [] { query, orderBy }) as IQueryable;
			}

			return (query);
		}

		public static IQueryable Take(this IQueryable query, Int32 pageSize)
		{
			Type propertyType = query.GetType().GetGenericArguments() [ 0 ];
			MethodInfo takeMethod = typeof(Queryable).GetMethod("Take", BindingFlags.Public | BindingFlags.Static).MakeGenericMethod(propertyType);

			query = takeMethod.Invoke(null, new Object [] { query, pageSize }) as IQueryable;

			return (query);
		}

		public static IQueryable Skip(this IQueryable query, Int32 pageIndex)
		{
			Type propertyType = query.GetType().GetGenericArguments() [ 0 ];
			MethodInfo skipMethod = typeof(Queryable).GetMethod("Skip", BindingFlags.Public | BindingFlags.Static).MakeGenericMethod(propertyType);

			query = skipMethod.Invoke(null, new Object [] { query, pageIndex }) as IQueryable;

			return (query);
		}
	}

Here are some examples:


IQueryable q = ...;

q = q.OrderBy("NAME asc", "BIRTHDAY desc");

q = q.WhereEquals("NAME", "bla");

q = q.GroupBy("PROFILE");

q = q.Take(10);

Bookmark and Share
Displaying Empty GridView

What happens if your GridView control does not have any records to display? Well, if you set the EmptyDataText or EmptyDataTemplate properties, the grid will show something, only probably not what you'd like - the normal structure, as if it was populated with records. Here's a possible solution:


void ShowEmptyRow(GridView grid)
{
	if (grid.Rows.Count == 0)
	{
		DataTable table = new DataTable();

		for (Int32 i = 0; i < grid.Columns.Count; ++i)
		{
			if (grid.Columns [ i ] is BoundField)
			{
				table.Columns.Add((grid.Columns [ i ] as BoundField).DataField);
			}
			else
			{
				table.Columns.Add(grid.Columns [ i ].SortExpression);
			}
		}

		table.Rows.Add(table.NewRow());

		grid.DataSourceID = String.Empty;
		grid.DataSource = table;
		grid.DataBind();

		if (String.IsNullOrEmpty(grid.EmptyDataText) == false)
		{
			grid.Rows [ 0 ].Cells [ 0 ].ColumnSpan = table.Columns.Count;
			grid.Rows [ 0 ].Cells [ 0 ].Text = grid.EmptyDataText;

			while (grid.Rows [ 0 ].Cells.Count > 1)
			{
				grid.Rows [ 0 ].Cells.RemoveAt(1);
			}
		}
	}
}

Bookmark and Share
Posted: Jun 25 2010, 11:39 AM by Ricardo Peres | with 3 comment(s)
Filed under: ,
NDepend 3

Patrick Smacchia, of NDepend fame, was kind enough to offer me a license for its latest version, 3.0, now fully integrated with Visual Studio 2010.

Its feature list is overwhelming, just think:

  • Visual representation of the code complexity over 82 code metrics
  • Dependency analysis between assemblies and types
  • API querying and reporting
  • Possibility to write custom rules on top of queries
  • Display code differences between different versions of assemblies

And much more. I will be posting a review as soon as I have the time for it, in the meanwhile, there's a video tour and a trial version available. Also, check out Patrick's blog. Bookmark and Share

Manually Seting a LINQ to SQL Entity's Timestamp

Suppose you want to manually persist your LINQ to SQL entities without an ObjectDataSource or LinqDataSource using the entities' timestamp column. You pick the timestamp, perhaps from an hidden field on your page, but you cannot change the System.Data.Linq.Binary property to another value, because the Binary won't allow it. You need to use reflection, here's how:


public void Update(Int32 key, String data, String timestamp)
{
  using (MyContext ctx = new MyContext())
  {
    MyEntity entity = ctx.MyEntities.Where(e => e.Key = key).Single();
    entity.Data = data;
    this.SetTimestamp(entity.RowVersion, timestamp);
    ctx.SubmitChanges();	//will throw an ChangeConflictException if the timestamps are different
  }
}

protected void SetTimestamp(Binary ts, String timestamp)
{
  bytesField.SetValue(ts, Convert.FromBase64String(timestamp.Replace("\"", String.Empty)));
  hashCodeField.SetValue(ts, null);
  computeHashMethod.Invoke(ts, null);
}

Bookmark and Share
June Visual Studio 2010 and .NET 4.0 Training Kit Released

The June release of the Visual Studio 2010 and .NET 4.0 Training Kit was released today.

Bookmark and Share
NHibernate 2 Beginner's Guide Review

OK, here's the review I promised a while ago.

This is a beginner's introduction to NHibernate, so if you have already some experience with NHibernate, you will notice it lacks a lot of concepts and information.

It starts with a good description of NHibernate and why would we use it. It goes on describing basic mapping scenarios having primary keys generated with the HiLo or Identity algorithms, without actually explaining why would we choose one over the other.

As for mapping, the book talks about XML mappings and provides a simple example of Fluent NHibernate, comparing it to its XML counterpart.

When it comes to relations, it covers one-to-many/many-to-one and many-to-many, not one-to-one relations, but only talks briefly about lazy loading, which is, IMO, an important concept. Only Bags are described, not any of the other collection types.

The log4net configuration description gets it's own chapter, which I find excessive.

The chapter on configuration merely lists the most common properties for configuring NHibernate, both in XML and in code.

Querying only talks about loading by ID (using Get, not Load) and using Criteria API, on which a paging example is presented as well as some common filtering options (property equals/like/between to, no examples on conjunction/disjunction, however).

There's a chapter fully dedicated to ASP.NET, which explains how we can use NHibernate in web applications. It basically talks about ASP.NET concepts, though. Following it, another chapter explains how we can build our own ASP.NET providers using NHibernate (Membership, Role).

The available entity generators for NHibernate are referred and evaluated on a chapter of their own, the list is fine (CodeSmith, nhib-gen, AjGenesis, Visual NHibernate, MyGeneration, NGen, NHModeler, Microsoft T4 (?) and hbm2net), examples are provided whenever possible, however, I have some problems with some of the evaluations: for example, Visual NHibernate scores 5 out of 5 on Visual Studio integration, which simply does not exist! I suspect the author means to say that it can be launched from inside Visual Studio, but then, what can't?

Finally, there's a chapter I really don't understand. It seems like a bag where a lot of things are thrown in, like NHibernate Burrow (which actually isn't explained at all), Blog.Net components, CSS template conversion and web.config settings related to the maximum request length for file uploads and ending with XML comments, with the help of GhostDoc.

Like I said, the book is only good for absolute beginners, it does a fair job in explaining the very basics, but lack a lot of not-so-basic concepts. Among other things, it lacks:

  • Inheritance mapping strategies (table per class hierarchy, table per class, table per concrete class)
  • Load versus Get usage
  • Other usefull ISession methods
  • First level cache (Identity Map pattern)
  • Other collection types other that Bag (Set, List, Map, IdBag, etc
  • Fetch options
  • User Types
  • Filters
  • Named queries
  • LINQ examples
  • HQL examples

And that's it! I hope you find this review useful. The link to the book site is https://www.packtpub.com/nhibernate-2-x-beginners-guide/book

Bookmark and Share
NHibernate 2 Beginner's Guide Book

Packt Publishing has recently released a new book on NHibernate: NHibernate 2 Beginner's Guide, by Aaron Cure. I am now reading the final version, which Packt Publishing was kind enough to provide me, and I will soon write about it. I can tell you for now that Fabio Maulo was one of the reviewers, which certainly raises the expectations. In the meanwhile, there's a free chapter you can download, which hopefully will get you interested in it; you can get it from here. Bookmark and Share

Table and Column Checksums

Following my last posts on Change Data Capture and Change Tracking, here is another tip regarding tracking changes: table and colum checksums.

The concept is: each time a column value changes, the checksum also changes. You can use this simple method to see if a table has changed very easily, however, beware, different column values may generate the same checksum. Here's the SQL:


-- table checksum
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableName

-- column checksum
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(ColumnName)) FROM TableName

-- integer column checksum
SELECT CHECKSUM_AGG(IntegerColumnName) FROM TableName

Here are the reference links on the CHECKSUM, CHECKSUM_AGG and BINARY_CHECKSUM functions:

Bookmark and Share
Change Tracking

You may recall my last post on Change Data Control. This time I am going to talk about other option for tracking changes to tables on SQL Server: Change Tracking.

The main differences between the two are:

  • Change Tracking works with SQL Server 2008 Express
  • Change Tracking does not require SQL Server Agent to be running
  • Change Tracking does not keep the old values in case of an UPDATE or DELETE
  • Change Data Capture uses an asynchronous process, so there is no overhead on each operation
  • Change Data Capture requires more storage and processing

Here's some code that illustrates it's usage:


-- for demonstrative purposes, table Post of database Blog only contains two columns, PostId and Title

-- enable change tracking for database Blog, for 2 days
ALTER DATABASE Blog
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- enable change tracking for table Post
ALTER TABLE Post
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

-- see current records on table Post
SELECT * FROM Post

SELECT * FROM sys.sysobjects WHERE name = 'Post'
SELECT * FROM sys.sysdatabases WHERE name = 'Blog'

-- confirm that table Post and database Blog are being change tracked
SELECT * FROM sys.change_tracking_tables
SELECT * FROM sys.change_tracking_databases

-- see current version for table Post
SELECT p.PostId,
	p.Title,
	c.SYS_CHANGE_VERSION,
	c.SYS_CHANGE_CONTEXT
FROM Post AS p
CROSS APPLY CHANGETABLE(VERSION Post, (PostId), (p.PostId)) AS c;

-- update post
UPDATE Post
SET Title = 'First Post Title Changed'
WHERE Title = 'First Post Title';

-- see current version for table Post
SELECT p.PostId,
	p.Title,
	c.SYS_CHANGE_VERSION,
	c.SYS_CHANGE_CONTEXT
FROM Post AS p
CROSS APPLY CHANGETABLE(VERSION Post, (PostId), (p.PostId)) AS c;

-- see changes since version 0 (initial)
SELECT p.Title, c.PostId, 
	SYS_CHANGE_VERSION,
	SYS_CHANGE_OPERATION,
	SYS_CHANGE_COLUMNS,
	SYS_CHANGE_CONTEXT 
FROM CHANGETABLE(CHANGES Post, 0) AS c
LEFT OUTER JOIN Post AS p
ON p.PostId = c.PostId;

-- is column Title of table Post changed since version 0?
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Post'), 'Title', 'ColumnId'), (SELECT SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES Post, 0) AS c))	

-- get current version
SELECT CHANGE_TRACKING_CURRENT_VERSION()

-- disable change tracking for table Post
ALTER TABLE Post
DISABLE CHANGE_TRACKING;

-- disable change tracking for database Blog
ALTER DATABASE Blog
SET CHANGE_TRACKING = OFF;

You can read about the differences between the two options here. Choose the one that best suits your needs!

Bookmark and Share
More Posts