p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 (http://p2p.wrox.com/forumdisplay.php?f=389)
-   -   Linq Problem (http://p2p.wrox.com/showthread.php?t=79691)

barakros June 9th, 2010 03:59 AM

Linq Problem
 
Hi Imar
I am trying to use linq and have the following problem:
I tried to do something like the example in chapter 13 with the Genres and Reviews , where we show the Genre and reviews in buletedlist inside a repeater

I am trying to do the same thing with my DB with 2 tables with a string common field.

I am also not using a SQL file but using a connection to the SQLEXPRESS
the provider is .NET Framework Data Provider for SQL Server
and the connection string :
Data Source=BARAK-LAPTOP\sqlexpress;Initial Catalog=Cosmo;Integrated Security=True

the problem is that I can't get the Child Table in the linq command
the intellisense does not show it and the link between the tables does not work - I made it as in the book example: parent table field to child table field on the same field (varchar(8))

also tried it with an integer field and also did not worked
linq does not support this provider?
any ideas?

TIA
BARAK

Imar June 9th, 2010 04:56 AM

Hi there,

Can you provide more information, such as:

1. Table structure (columns, types etc)
2. The way you linked the tables together in the database
3. The way you added the table to the LINQ diagram
4. The code / LINQ query you have tried so far
5. The LINQ query you want to / think you can write

Cheers,

Imar

barakros June 9th, 2010 08:05 AM

thanks
 
here it is:
DB script:
Code:

USE [Store]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 06/09/2010 14:59:07 ******/
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,
 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]) VALUES (N'111111', CAST(0xEA320B00 AS Date))
INSERT [dbo].[Orders] ([sono], [sodate]) VALUES (N'222222', CAST(0xE6320B00 AS Date))
/****** Object:  Table [dbo].[Tracks]    Script Date: 06/09/2010 14:59:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tracks](
        [sono] [varchar](8) NULL,
        [track] [varchar](50) NULL,
        [boxno] [smallint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Tracks] ([sono], [track], [boxno]) VALUES (N'111111', N'AAAAA', 1)
INSERT [dbo].[Tracks] ([sono], [track], [boxno]) VALUES (N'111111', N'BBBBBB', 2)
INSERT [dbo].[Tracks] ([sono], [track], [boxno]) VALUES (N'222222', N'CCCCC', 1)
/****** Object:  ForeignKey [FK_Tracks_Orders]    Script Date: 06/09/2010 14:59:07 ******/
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

I also have a diagram according to the sono field connection

the connection string:
Data Source=BARAK-LAPTOP\SQLEXPRESS;Initial Catalog=Store;Integrated Security=True
Provider:
.NET Framework Data Provider for SQL Server

I drag the 2 tables to the DBML file (DataClasses.dbml) and the diagram look ok with an arrow from orders to tracks (OneToMany)

the code behind the page
Code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Using myDataContext As New DataClassesDataContext()
      Dim allOrders = From order In myDataContext.Orders _
                      Order By order.sodate _
                      Select New With {order.sono,order.?} 'need here the Tracks

      Repeater1.DataSource = allOrders
      Repeater1.DataBind()
    End Using
  End Sub

the asp.net code:
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">
  <asp:Repeater ID="Repeater1" runat="server">
    <ItemTemplate>
      <asp:Literal ID="Literal1" runat="server" Text='<%#Eval("sono") %>'></asp:Literal>
      <asp:BulletedList ID="BulletedList1" runat="server" DisplayMode="Text"
      DataTextField="track" DataSource='<%#Eval("Tracks") %>'>
      </asp:BulletedList>
    </ItemTemplate>
  </asp:Repeater>
  </form>
</body>
</html>


don't know waht more to add here

thanks so much

Barak

barakros June 9th, 2010 08:36 AM

I found a way to make it work
 
I added an id field to the Tracks table set it to be identity and PK
I recreated the DBML diagram and it worked
but it looks stupid to have a field that I don't need

what you think?

TIA
Barak

Imar June 9th, 2010 09:10 AM

I was just going to suggest that after I tried your SQL script ;-)

LINQ to SQL needs some way to uniquely identify an object. It uses the primary key for that. Otherwise, it cannot track objects.

You don't *have* to add a surrogate primary key (e.g. one that's there purely to make records unique). If you can create one from the columns in the Track table, that's OK too. For example, if sono and track together are unique (e.g. sono can occur multiple times, with unique values for track), then you can create a composite key. To do this, open the database diagram, select both the sono and track columns and click the key icon. Then recreate the LINQ to SQL diagram and your Tracks collection appears for the Order object. You may want to / have to rename the track column as having a class name that is also a property name will get you into troubles.

FYI:
Quote:

don't know waht more to add here
Nothing, This is exactly the type of information needed for a good post and a quick answer.

Cheers,

Imar

barakros June 9th, 2010 09:41 AM

thanks
 
Hi imar
thanks for your reply

I tried to do that in another way but I did not knew that you can select 2 fields and set them as key - every day you learn something!

good day
BARAK

barakros June 9th, 2010 02:57 PM

another question about linq
 
Hi
one of the example in the book is about Reviews and Genre,where the linq is from the Reviews
Code:

From myReviews im myDatacontedxt.Reviews _
Where myReview.Authorized=True _
Select New With {myReview.Id,myReview.Genre.name}

I want to do something like it but need Grouping.
try to look for it on the web but the syntax is not clear to me how to group and SUM for example

can you give an example or reference?

TIA
Barak

Imar June 10th, 2010 02:55 AM

Quote:

I want to do something like it but need Grouping.
Just as with your previous post: please be specifc. Tell us a bit more about your structure, post some code you have tried or pseudo code you want to create and tell us more about the final resul you're after. "I need grouping" doesn't really classift as a clear problem description. The more detailed your initial post, the better the answer and the less time we're all spending on posting messages asking for clarification.

Also, this is getting a bitt off-topic for this book's forum, so posting in a more appropriate forum (LINQ, .NET general) might attract more viewers and this possible answers.

Cheers,

Imar

barakros June 10th, 2010 06:03 AM

Sorry
 
Hi imar
I post it here :

http://p2p.wrox.com/asp-net-3-5-prof...tml#post258953

I will be glad if you can look at it

also I manage to this in another way with 2 SQL query
1 to get the orders and for each order get the tracks
but that a lot of queries - if there will be 100 orders I will have 100 queries runing to get the tracks - does not sounds good!

anyway thanks
good day
BARAK


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

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