Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Professionals If you are an experienced ASP.NET programmer, this is the forum for your 3.5 questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Professionals section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 10th, 2010, 06:00 AM
Authorized User
Points: 154, Level: 3
Points: 154, Level: 3 Points: 154, Level: 3 Points: 154, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2010
Posts: 34
Thanks: 5
Thanked 0 Times in 0 Posts
Default Linq Question

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
  #2 (permalink)  
Old June 10th, 2010, 02:38 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #3 (permalink)  
Old June 14th, 2010, 08:54 AM
Authorized User
Points: 154, Level: 3
Points: 154, Level: 3 Points: 154, Level: 3 Points: 154, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2010
Posts: 34
Thanks: 5
Thanked 0 Times in 0 Posts
Default hi

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
  #4 (permalink)  
Old June 14th, 2010, 01:49 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #5 (permalink)  
Old June 15th, 2010, 05:22 AM
Authorized User
Points: 154, Level: 3
Points: 154, Level: 3 Points: 154, Level: 3 Points: 154, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2010
Posts: 34
Thanks: 5
Thanked 0 Times in 0 Posts
Default OK. I will try

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
  #6 (permalink)  
Old June 15th, 2010, 05:28 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #7 (permalink)  
Old June 15th, 2010, 09:20 PM
Friend of Wrox
Points: 1,749, Level: 16
Points: 1,749, Level: 16 Points: 1,749, Level: 16 Points: 1,749, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2007
Location: San Diego, CA, USA.
Posts: 477
Thanks: 10
Thanked 19 Times in 18 Posts
Default

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?
__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.
  #8 (permalink)  
Old June 16th, 2010, 01:46 AM
Authorized User
Points: 154, Level: 3
Points: 154, Level: 3 Points: 154, Level: 3 Points: 154, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2010
Posts: 34
Thanks: 5
Thanked 0 Times in 0 Posts
Default hi

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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linq Question about related entities gspro BOOK: ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solutio 0 March 23rd, 2010 09:29 AM
Linq paging Question barakros BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 3 January 27th, 2010 11:48 AM
Question about LINQ maratin BOOK: ASP.NET MVC Website Programming Problem Design Solution ISBN: 9780470410950 3 January 24th, 2010 10:49 AM
Linq - A Design Question mikener LINQ 3 November 14th, 2009 10:47 AM
LINQ question thangxii BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 6 July 6th, 2008 03:13 PM



All times are GMT -4. The time now is 08:29 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.