) worked great! I think I will see if I can catch the type of the first "row" on what gets passed in, and if it is already a data table, just pass the reference right back (or at least be more elegant about failing...)
Now I just need to figure out how the code all works... I'm not used to writing delegates and such to define the types that are then used by the other methods -- that's a whole new intriguing layer!
Thanks again!
JoeK
>Hi, Joe,
>
>A couple of things.
>
>First, you can combine both queries as follows. I created two ADO.NET datatables that are similar to what you posted:
>
>>DataTable dt1 = new DataTable();
>dt1.Columns.Add("DebtorNum", typeof(Int32));
>dt1.Columns.Add("DebtorName", typeof(String));
>dt1.Columns.Add("DebtorState",typeof(String));
>
>dt1.Rows.Add(1,"ABC","PA");
>dt1.Rows.Add(2,"DEF","GA");
>dt1.Rows.Add(3,"GHI","IL");
>dt1.Rows.Add(4,"JKL","IL");
>dt1.Rows.Add(5,"MNO","TX");
>
>DataTable dt2 = new DataTable();
>dt2.Columns.Add("DebtorNum",typeof(Int32));
>dt2.Columns.Add("MoneyOwed",typeof(Decimal));
>dt2.Columns.Add("CaseNum",typeof(String));
>
>dt2.Rows.Add(1,100,"11111");
>dt2.Rows.Add(2,200,"22222");
>dt2.Rows.Add(3,300,"33333");
>dt2.Rows.Add(3,400,"44444");
>dt2.Rows.Add(4,500,"55555");
>dt2.Rows.Add(4,600,"66666");
>
>var Results = from dtDebtor1 in dt1.AsEnumerable()
> where dtDebtor1.Field<string>("DebtorState") == "IL"
> join dtDebtor2 in dt2.AsEnumerable()
> on dtDebtor1.Field<Int32>("DebtorNum") equals dtDebtor2.Field<Int32>("DebtorNum")
> select new {
> DebtorNum = dtDebtor1.Field<Int32>("DebtorNum"),
> DebtorName = dtDebtor1.Field<string>("DebtorName"),
> MoneyOwed = dtDebtor2.Field<decimal>("MoneyOwed"),
> CaseNum = dtDebtor2.Field<String>("CaseNum")
> };
>>
>Now, I'll be honest, I prefer strongly-typed datasets or custom collections (using the .NET List class) over untyped datasets. Untyped datasets are a royal pain to work with in LINQ. But that's how you'd do the query.
>
>To answer the second question....if you want to take the result set and pump it back into an ADO.NET DataTable....there was a function in an early CTP of VS2008 to copy the results of a LINQ query to an ADO.NET table. Microsoft took it out, and so I (and many others) wrote a helper function to perform the work of such a function.
>
>Here's an extension method called ToADOTable...(obviously, you could write code every time to loop through your result var and create a datatable manually, but the following is more of a generic approach. It uses reflection to determine the columns from the initial results:
>
>>using System;
>using System.Collections.Generic;
>using System.Linq;
>using System.Text;
>using System.Data;
>using System.Reflection;
>
>namespace LinqSamples
>{
> public static class VarToTable
> {
> public static DataTable ToADOTable<T>(
> this IEnumerable<T> varlist, NewRowCreateDelegate<T> fn)
> {
>
> DataTable dtReturn = new DataTable();
>
> T TopRec = varlist.ElementAt(0);
> PropertyInfo[] oProps = ((Type)TopRec.GetType()).GetProperties();
> for (int i = 0; i < oProps.Length; i++)
> dtReturn.Columns.Add(oProps[i].Name, oProps[i].PropertyType);
>
> foreach (T rec in varlist)
> {
> DataRow dr = dtReturn.NewRow();
>
> foreach (PropertyInfo pi in oProps)
> dr[pi.Name] = pi.GetValue(rec, null);
> dtReturn.Rows.Add(dr);
> }
>
> return (dtReturn);
> }
> public delegate object[] NewRowCreateDelegate<T>(T t);
> }
>}
>
>>
>Finally, to use this extension method, you can do the following, which uses the Results anonymous type from the first query.
>
>> DataTable dt = Results.ToADOTable(rec => new object[] { Results });
> dt.TableName = "MyResults";
>>
>
>Now, I just discovered that the final release of VS2008 has a CopyToDataTable method back in. I tried it and it didn't immediately work for me in this instance. I'm taking a wild guess that the "stock" framework method doesn't allow loading instances of anonymous types. I want to look at this more, but either way, the extension method I've included should work.
>
>Hope this helps...
>Kevin
