Linq Question

barakros · June 10th, 2010, 06:00 AM

Hi
I want to create a Linq query according to the following DB Structure
I want to get (Select) : sono,sodate,sum(qty*price) as sototal,tracks.
I want the tracks to be returned an object that I can pass to a datalist object
so all the tracks lines will show in the datalist items (1 track=1 datalist item)

and also to filter by custno and order by sodate

because there are 3 tables involved I can decide from where to query and how
because in order to see SUM I need to group by sono and the syntax of Grouping in Linq is not clear to me

the connection beteen the tables is based on sono field

here is the SQL script of the DB:

Code:

USE [Store]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 06/10/2010 12:29:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
	[sono] [varchar](8) NOT NULL,
	[sodate] [date] NULL,
	[custno] [varchar](6) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[sono] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Orders] ([sono], [sodate], [custno]) VALUES (N'111111', CAST(0xEA320B00 AS Date), N'Cust1')
INSERT [dbo].[Orders] ([sono], [sodate], [custno]) VALUES (N'222222', CAST(0xE6320B00 AS Date), N'Cust2')
INSERT [dbo].[Orders] ([sono], [sodate], [custno]) VALUES (N'333333', CAST(0xE9320B00 AS Date), N'Cust1')
/****** Object:  Table [dbo].[Tracks]    Script Date: 06/10/2010 12:29:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tracks](
	[sono] [varchar](8) NOT NULL,
	[boxno] [smallint] NOT NULL,
	[track] [varchar](50) NULL,
 CONSTRAINT [PK_Tracks] PRIMARY KEY CLUSTERED 
(
	[sono] ASC,
	[boxno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Tracks] ([sono], [boxno], [track]) VALUES (N'111111', 1, N'AAAAA')
INSERT [dbo].[Tracks] ([sono], [boxno], [track]) VALUES (N'111111', 2, N'BBBBBB')
INSERT [dbo].[Tracks] ([sono], [boxno], [track]) VALUES (N'222222', 1, N'CCCCC')
/****** Object:  Table [dbo].[Lines]    Script Date: 06/10/2010 12:29:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Lines](
	[sono] [varchar](8) NOT NULL,
	[item] [varchar](15) NOT NULL,
	[qty] [int] NULL,
	[price] [float] NULL,
 CONSTRAINT [PK_Lines] PRIMARY KEY CLUSTERED 
(
	[sono] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'111111', N'It1', 1, 12)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'111111', N'It2', 2, 13)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'111111', N'It4', 1, 11)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'222222', N'It3', 3, 10)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'333333', N'It1', 6, 9)
/****** Object:  ForeignKey [FK_Tracks_Orders]    Script Date: 06/10/2010 12:29:32 ******/
ALTER TABLE [dbo].[Tracks]  WITH CHECK ADD  CONSTRAINT [FK_Tracks_Orders] FOREIGN KEY([sono])
REFERENCES [dbo].[Orders] ([sono])
GO
ALTER TABLE [dbo].[Tracks] CHECK CONSTRAINT [FK_Tracks_Orders]
GO
/****** Object:  ForeignKey [FK_Lines_Orders]    Script Date: 06/10/2010 12:29:32 ******/
ALTER TABLE [dbo].[Lines]  WITH CHECK ADD  CONSTRAINT [FK_Lines_Orders] FOREIGN KEY([sono])
REFERENCES [dbo].[Orders] ([sono])
GO
ALTER TABLE [dbo].[Lines] CHECK CONSTRAINT [FK_Lines_Orders]
GO

here is the aspx:

Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
      <Columns>
        <asp:BoundField DataField="sono" HeaderText="Order #" />
        <asp:BoundField DataField="sodate" HeaderText="Date" />
        <asp:BoundField DataField="Total" HeaderText="Total" />
        <asp:TemplateField HeaderText="track">
          <ItemTemplate>
            <asp:DataList ID="rptTrack" runat="server" RepeatDirection="Vertical"
             DataSource='<%#Eval("Tracks") %>'
              CellSpacing="1" Font-Size="12px" GridLines="None">
              <ItemTemplate>
                <asp:HyperLink Target="_blank" ID="hl1" Text='<%#Eval("track")%>' runat="server"
                  NavigateUrl='<%#GetTrackURL(Eval("track")) %>' />
              </ItemTemplate>
            </asp:DataList>
          </ItemTemplate>
        </asp:TemplateField>
      </Columns>
    </asp:GridView>
  </div>
  </form>
</body>
</html>

here is the code behind:

Code:

Partial Class _Default
    Inherits System.Web.UI.Page
  Protected Function GetTrackURL(ByVal aTrack As String) As String
    Return "http://www.track.com?track=" & aTrack
  End Function

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Using dc As New DataClassesDataContext()
      Dim allOrders = From line In dc.Lines _
              Order By line.Order.sodate _
              Where line.Order.custno = "Cust1" _
             Group By line.sono, line.Order.sodate Into _
             Total = Sum(line.price * line.qty), _
             Tracks = SelectMany(line.Order.Tracks) _
             Select sono, sodate, Total, Tracks

      GridView1.DataSource = allOrders
      GridView1.DataBind()
    End Using
  End Sub
End Class

I get results not in the correct order
and the tracks come too much times
I used SelectMany since it the only way I found to compile this

Is there a way to get this or its too complicatd?

Thanks a lot
BARAK

Imar · June 10th, 2010, 02:38 PM

I don't understand your requirements, so I can't help you direclty with a LINQ query. But mabe these LINQ samples will get you there?

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Imar

barakros · June 14th, 2010, 08:54 AM

hi Imar
I looked at this samples before but did not helped me....

I have a table of Orders and I want for each order to see the fields:
sono,sodate and for each order to see the total amount (price*qty) from Lines table, and also to see the tracks that can be more than 1 for each orders

so its involved 3 tables from each I need diffrent info.

for example from the DB I attached the script:
for custno='Cust1' i wabt to get:
sono=111111
sodate=2010-06-08
Total:49
Tracks: AAAAA,BBBBB (which is a list of 2 tracks and not a single field)
like we have on the book in chapter 13 with Genre and Reviews

hope I made myself more clear

BARAK

Imar · June 14th, 2010, 01:49 PM

Quote:

hope I made myself more clear

I am sorry, but not really. All this AAAA, BBBB and 1111 stuff doesn't make things any easier.

How about posting some real (expected) data?

Imar

barakros · June 15th, 2010, 05:22 AM

the data is not realy important
Orders table holds sono as sale order number, date and customer number
the table key is sono

each sale order has lines in Lines tables with: (sono, item) as key and qty orderd and the price

each sale order has records of tracking (0 or more)
the Tracks table holds sono, boxno and track which is a tracking reference for that box.

I want to get the Orders.sono,Orders.sodate,total of the sale order amount which is (Lines.qty*Lines.price) for that Orders.sono and to get the List of Tracks.track for that sono from Tracks table
Order by Orders.sodate and for specific Orders.custno

I am posting a new script for the DB
I hope that will help
thanks again

Barak

Code:

USE [Store]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 06/15/2010 12:19:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
	[sono] [varchar](8) NOT NULL,
	[sodate] [date] NULL,
	[custno] [varchar](6) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[sono] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Orders] ([sono], [sodate], [custno]) VALUES (N'Order1-1', CAST(0xF8310B00 AS Date), N'Cust1')
INSERT [dbo].[Orders] ([sono], [sodate], [custno]) VALUES (N'Order2-1', CAST(0xF7310B00 AS Date), N'Cust1')
INSERT [dbo].[Orders] ([sono], [sodate], [custno]) VALUES (N'Order3-2', CAST(0xF5310B00 AS Date), N'Cust2')
/****** Object:  Table [dbo].[Tracks]    Script Date: 06/15/2010 12:19:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tracks](
	[sono] [varchar](8) NOT NULL,
	[boxno] [smallint] NOT NULL,
	[track] [varchar](50) NULL,
 CONSTRAINT [PK_Tracks] PRIMARY KEY CLUSTERED 
(
	[sono] ASC,
	[boxno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Tracks] ([sono], [boxno], [track]) VALUES (N'Order1-1', 1, N'TrackRef1-Order1-1')
INSERT [dbo].[Tracks] ([sono], [boxno], [track]) VALUES (N'Order1-1', 2, N'TrackRef2-Order1-1')
/****** Object:  Table [dbo].[Lines]    Script Date: 06/15/2010 12:19:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Lines](
	[sono] [varchar](8) NOT NULL,
	[item] [varchar](15) NOT NULL,
	[qty] [int] NULL,
	[price] [float] NULL,
 CONSTRAINT [PK_Lines] PRIMARY KEY CLUSTERED 
(
	[sono] ASC,
	[item] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'Order1-1', N'Item1', 1, 12)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'Order1-1', N'Item2', 2, 11)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'Order2-1', N'Item3', 1, 18)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'Order3-2', N'Item1', 2, 12)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'Order3-2', N'Item2', 1, 8)
INSERT [dbo].[Lines] ([sono], [item], [qty], [price]) VALUES (N'Order3-2', N'Item3', 3, 7)
/****** Object:  ForeignKey [FK_Tracks_Orders]    Script Date: 06/15/2010 12:19:40 ******/
ALTER TABLE [dbo].[Tracks]  WITH CHECK ADD  CONSTRAINT [FK_Tracks_Orders] FOREIGN KEY([sono])
REFERENCES [dbo].[Orders] ([sono])
GO
ALTER TABLE [dbo].[Tracks] CHECK CONSTRAINT [FK_Tracks_Orders]
GO
/****** Object:  ForeignKey [FK_Lines_Orders]    Script Date: 06/15/2010 12:19:40 ******/
ALTER TABLE [dbo].[Lines]  WITH CHECK ADD  CONSTRAINT [FK_Lines_Orders] FOREIGN KEY([sono])
REFERENCES [dbo].[Orders] ([sono])
GO
ALTER TABLE [dbo].[Lines] CHECK CONSTRAINT [FK_Lines_Orders]
GO

Imar · June 15th, 2010, 05:28 AM

I hate to say it but I still don't really get it. Data *is* important as it helps in understanding the input and outcome.

I am sure this is solvable with LINQ somehow, but figuring out what it is that you want / need is going to take me too much time and as such goes beyond what I can do in a forum.

Sorry; hopefully someone else here can help you out?

Cheers,

Imar

chroniclemaster1 · June 15th, 2010, 09:20 PM

Fundamentally, a program has only one purpose, to generate output. If we don't know what output the program is supposed to generate, then we don't know how to help you get there. Can you copy a portion of your requirements document that shows what the output is supposed to look like? Do you have a requirements document?

barakros · June 16th, 2010, 01:46 AM

I don't have a document but I can try yo explain again

according to the data script I attached:
for the Orders table first record :Order1-1
the total amount is 34 (1*12+2*11 in Lines table) and the order has 2 tracking records in Tracks table (2 boxes each has its own track reference): TrackRef1-Order1-1 and TrackRef2-Order1-1.

the output need to be like this:

HTML Code:

<html><table border=1>
<tr>
<td>SONO
<TD>DATE
<TD>AMOUNT
<TD>TRACKING
</TR>
<tr>
<td>Order1-1
<TD>06/08/2010
<TD>34
<TD><TABLE><TR><TD>TrackRef1-Order1-1<TR><TD>TrackRef1-Order1-1</TD></TR></TABLE>
</TR>
</TABLE></html>

I tried to create the linq question after creating the Linq diagram but I need (I think) to use grouping here and can not figure out how to do that

thanks
Barak