Linq Problem

barakros · June 9th, 2010, 03:59 AM

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

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

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

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

Hi imar
I post it here :

Linq Question

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