asked on

LINQ Query not working

I have a SQL Query that put into a datatable.
I have a manually configured datatable.
I'm trying to merge the data using LINQ but I get no data and/or errors.

The SQL Query returns:
logHour logDate
00 2/1/2011
02 2/1/2011
05 2/1/2011

The Datatable returns:
00
01
02
03
04
05

I want to combine to make:
00 2/1/2011
01 <null>
02 2/1/2011
03 <null>
04 <null>
05 2/1/2011

I'm using the following LINQ but I get an error when binding it to the gridview:
Error: "The data source for GridView with id 'gv' did not have any properties or attributes from which to generate columns. Ensure that your data source has content. "

Query = (From h In dt2 _
Group Join data In dt _
On h.Field(Of String)("logHour") Equals _
data(0) _
Into children = Group _
From child In children.DefaultIfEmpty())

gv.DataSource = Query
gv.DataBind()

'Code to GET SQL
da = New SqlDataAdapter(GetSelect(), con)
Dim ds As New DataSet, dt As New DataTable("data")   
da.Fill(dt)

'Code for manual datatable
 dt2 As New DataTable()
 dt2 = dtHours()

'LINQ
Dim Query = (From h In dt2 _
                Group Join data In dt _
                On h.Field(Of String)("logHour") Equals _
                   data(0) _
              Into children = Group _
        From child In children.DefaultIfEmpty())

            gv.DataSource = Query
            gv.DataBind()

Private Function dtHours() As DataTable
        Dim dt As New DataTable, dr As DataRow
        Dim i As Integer = 0
        dt.Columns.Add("logHour", Type.GetType("System.String"))
        While i <= 5
            dr = dt.NewRow
            If i < 10 Then
                dr("logHour") = "0" & i.ToString
            Else
                dr("logHour") = i.ToString
            End If

            dt.Rows.Add(dr)
            i = i + 1
        End While
        Return dt
    End Function

Open in new window

Pryrates

I made a litte C# sample.
Sorry not so into vb-net, but i think the idea is the same:

public string result;
        protected void Page_Load(object sender, EventArgs e)
        {
            List<string> lstH = new List<string>();
            for (int i = 0; i <= 5; i++)
            {
                lstH.Add (i.ToString("00"));
            }

            List<SqlVals> lst = new List<SqlVals>();
            lst.Add(new SqlVals("00", "2/1/2011"));
            lst.Add(new SqlVals("02", "2/1/2011"));
            lst.Add(new SqlVals("05", "2/1/2011"));


            var x = (from h in lstH
                      join p in lst on h equals p.logHour into j 
                      from subset in j.DefaultIfEmpty()
                     select new { logDate = (subset == null ? string.Empty : subset.logDate) , h  });

            foreach (var v in x)
            {
                result += "<br />" + v.h + " " + (string.IsNullOrEmpty(v.logDate) ? "<null>" : v.logDate);
            }
        }

        class SqlVals
        {
            public string logHour { get; set; }
            public string logDate { get; set; }
            public SqlVals(string lh, string ld)
            {
                this.logHour = lh;
                this.logDate = ld;
            }
        }

Open in new window

Hope that helps.
Maybe you will need to "convert" the datatables into the structures I use (one List of string and one class containing logHours and date)

I embedded it into a website an displayed "result" on it. It looks exacly the wayyou want it to be :)

robnhood00

ASKER

Thanks Pryrates. I think that would work fine, however, I have many more fields and multiple reports that I need to make. LINQ seems like the best option for managing the data. Anyway to make the LINQ query work?

ASKER CERTIFIED SOLUTION

Pryrates

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robnhood00

ASKER

Thanks!