RSS

Convert LINQ to Entity ObjectQuery to DataTable

06 Jul

It has been a while, I know and I have been meaning to blog but have been uber busy so here is a new one for you.
So for anyone who has used LINQ to DataSet you know there is a method for CopyToDataTable(). For LINQ to Entity there is no such thing and though I will not go into why there are reasons and they make partial sense but I needed to do this to get better control of my data binding. So I went out and used reflection and came back with a simple way to convert the returned IEnumerable object list to a DataTable this treats each object in the returned entity as a DataRow and dynamically gets the properties and the column names. Below are the C# and VB.Net versions. Also make sure to include the System.Reflection Library in your class where you use this.

    Public Function ObjectQueryToDataTable(Of T)(objlist As IEnumerable(Of T)) As DataTable
        Dim dtReturn As New DataTable()

        Dim objProps As PropertyInfo() = Nothing

        If objlist Is Nothing Then
            Return dtReturn
        End If

        For Each objRec As T In objlist
            If objProps Is Nothing Then
                objProps = DirectCast(objRec.[GetType](), Type).GetProperties()
                For Each objpi As PropertyInfo In objProps
                    Dim colType As Type = objpi.PropertyType

                    If (colType.IsGenericType) AndAlso (colType.GetGenericTypeDefinition() = GetType(Nullable(Of ))) Then
                        colType = colType.GetGenericArguments()(0)
                    End If

                    dtReturn.Columns.Add(New DataColumn(objpi.Name, colType))
                Next
            End If

            Dim dr As DataRow = dtReturn.NewRow()

            For Each pi As PropertyInfo In objProps
                dr(pi.Name) = If(pi.GetValue(objRec, Nothing) Is Nothing, DBNull.Value, pi.GetValue(objRec, Nothing))
            Next

            dtReturn.Rows.Add(dr)
        Next
        Return dtReturn
    End Function
public DataTable ObjectQueryToDataTable<T>(IEnumerable<T> objlist)
{
	DataTable dtReturn = new DataTable();

	PropertyInfo[] objProps = null;

	if (objlist == null) {
		return dtReturn;
	}

	foreach (T objRec in objlist) {
		if (objProps == null) {
			objProps = ((Type)objRec.GetType()).GetProperties();
			foreach (PropertyInfo objpi in objProps) {
				Type colType = objpi.PropertyType;

				if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>))) {
					colType = colType.GetGenericArguments()(0);
				}

				dtReturn.Columns.Add(new DataColumn(objpi.Name, colType));
			}
		}

		DataRow dr = dtReturn.NewRow();

		foreach (PropertyInfo pi in objProps) {
			dr(pi.Name) = pi.GetValue(objRec, null) == null ? DBNull.Value : pi.GetValue(objRec, null);
		}

		dtReturn.Rows.Add(dr);
	}
	return dtReturn;
}

Hope this helps you out as always if you have a better way let me know :).

Advertisements
 
2 Comments

Posted by on July 6, 2012 in ASP.Net, C#, Entity Framework, SQL

 

Tags: , , ,

2 responses to “Convert LINQ to Entity ObjectQuery to DataTable

  1. Rob

    December 14, 2012 at 12:34 am

    Useful. Some of your c# code is a bit dodgy though:
    1. colType = colType.GetGenericArguments()(0); <<<< [0] ?
    2. dr(pi.Name) <<<< dr[pi.Name]

    Thx, Rob

     
    • atomiccode

      January 2, 2013 at 7:14 pm

      the original was in vb and it did not convert brackets correctly my apologies.

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: