a bug or a fault?
using
System;using
System.Collections.Generic;using
System.Linq;using
System.Text;namespace
ConsoleApplication30{
classProgram{
staticvoid Main(string[] args){
var db =newDBOEODataContext();var persons1 = db.ExecuteQuery<PERSON>("SELECT _NAME FROM PERSON");var persons2 = db.ExecuteQuery<PERSON>("SELECT * FROM PERSON");}
}
}
I get an ERROR
The required column '[_ID]' does not exist in the results.but it does exist in the linQ to sql designer.
what should I do ?
Hello,
The problem relates to
var persons1 = db.ExecuteQuery<PERSON>("SELECT _NAME FROM PERSON");
As part of the query, you must return the ID field for the table, in this case you are only returning the name.
This would work:
var persons1 = db.ExecuteQuery<PERSON>("SELECT _ID, _NAME FROM PERSON");
Linq uses the AutoIncrement int ID field for tracking the objects internally (I think).
When using this approach, you are executing SQL directly on the server and then the resultset is being converted back to IEnumerable<Person>. The designer is just a representation of the <Person> object, but in this case, only the ID and Name will be populated.
Hope this makes sense.
Ben
http://www.onlineofisi.net/IMAGES/ERDEM/bug.png
please check this out....everything is there?
var db = new DBOEODataContext();
var persons1 = db.ExecuteQuery<PERSON>("SELECT count(*) FROM PERSON");
even this does not work gives the same error
Big screen.....
Like I said, your design is correct however when you are executing the query (SELECT _NAME) only the Name of the person is being returned.
When linq tries to convert this to an actual Person object it must have the ID for that person as part of the record set being returned from the database.
The fix is to return the ID aswell as the Name as part of your query.
Ben
I do it does not matter.
var db = new DBOEODataContext(); var persons1 = db.ExecuteQuery<PERSON>("SELECT _ID FROM PERSON"); it still thorws the same error that is why ? suspect this as a bug.
it does not matter which query I do use they all return the same error....
Ahh I see.
_ID != [_ID].
Having the [] around the column name is causing confusion for linq (sounds like a bug to me) as it cannot link the column name _ID from the table to the name [_ID] in the designer.
You can't even do SELECT [_ID] FROM PERSON as it doesn't even get as far as the database as the check is done first? Not sure.
Fix: Remove the [] around the Source property. To me, it does sound like a bug as keywords require the [] so they will have to link somehow. EDIT: Maybe not a bug because the table doesn't have [] in the name, thats only used for querying. How did you end up with [] ? Did you add them or the designer?
Sorry I missed it before.
Ben
Hi
Can you please give the table schema? The table schema shown through the LINQ to SQL designer on your link is not specific enough.
I've tried to repro this using this schema:
create Table
MyTable ([Id] int Identity(1,1) NOT NULL PRIMARY KEY, Name nvarchar(50)) go
And the LINQ to SQL designer created a mapping file with the property _Id with this column attribute:
[
Column(Name="[_Id]" When I removed the square brackets, from this column attribute to this:
[Column(Name="_Id"
The column was properly populated. If your mapping file has this same issue, can you remove it and see if that solves your problem?
Thanks,
Kathy
I got the error....if remove the identity property over the LinQ to sql designer it works fine but it crashes other parts of my application....I guess ExecuteQuery method needs to be fixed.
USE
[OEO] GO
/****** Object: Table [dbo].[PERSON] Script Date: 08/09/2007 22:40:43 ******/
SET
ANSI_NULLS ON GO
SET
QUOTED_IDENTIFIER ON GO
CREATE
TABLE [dbo].[PERSON]( [_ID] [int]
IDENTITY(1,1) NOT NULL, [_OWNERID] [int]
NOT NULL, [_LASTMODIFYDATE] [datetime]
NOT NULL, [_CREATEDATE] [datetime]
NOT NULL, [_DELETIONSTATUS] [tinyint]
NOT NULL CONSTRAINT [DF_PERSON__ENTITYSTATUS] DEFAULT ((0)), [_SECURITYCODE] [tinyint]
NOT NULL CONSTRAINT [DF_PERSON__SECURITYCODE] DEFAULT ((0)), [_NAME] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [_STATE] [nvarchar]
(20) COLLATE Turkish_CI_AS NOT NULL, [_DESCRIPTION] [nvarchar]
(max) COLLATE Turkish_CI_AS NOT NULL, [_CATEGORY] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [_TYPE] [nvarchar]
(20) COLLATE Turkish_CI_AS NOT NULL, [LASTNAME] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [WORKSTATUS] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [SALUTATION] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [YEARSOFEXPERIENCE] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [GENDER] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [PROFESSION] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [NICKNAME] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [TITLE] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL CONSTRAINT [DF_PERSON_TITLE] DEFAULT (''), [DEPARTMENT] [nvarchar]
(100) COLLATE Turkish_CI_AS NOT NULL, [PREFIX] [nvarchar]
(50) COLLATE Turkish_CI_AS NOT NULL, [HOMETEL] [nvarchar]
(100) COLLATE Turkish_CI_AS NOT NULL, [FAXTEL] [nvarchar]
(100) COLLATE Turkish_CI_AS NOT NULL, [MOBILETEL] [nvarchar]
(100) COLLATE Turkish_CI_AS NOT NULL, [EMAIL] [nvarchar]
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [WEBADDRESS] [nvarchar]
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [STREETNAME] [nvarchar]
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DISTRICTNAME] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TOWNNAME] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CITYNAME] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [COUNTRYNAME] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [POSTALCODE] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LEADSOURCE] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [BIRTHDAY] [datetime]
NULL, [RECEIVEEMAIL] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RECEIVEINFORMATIONEMAIL] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RECEIVECRAWLERESTATEUPDATE] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RECEIVEMYESTATEUPDATE] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RECEIVEMORTGAGEINFORMATION] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [WORKINGORGANIZATIONID] [int]
NULL, [REPORTSTOPERSONID] [int]
NULL, [MEMBERID] [bigint]
NULL, [DTSID] [int]
NULL, CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED (
[_ID]
ASC )
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] )
ON [PRIMARY]
I got the point when ? cahnge the name _ID to something like XXXX .it works. but when it is named _ID it crashes.....
please don't tell me I can't name my db coloun in that way .....this will end my Linq migration.
var persons1 = db.ExecuteQuery<PERSON>("SELECT _NAME FROM PERSON");
This query is not returning PERSON, but strings. So you should change it to:
var persons1 = db.ExecuteQuery<string>("SELECT _NAME FROM PERSON");
var persons2 = db.ExecuteQuery<PERSON>("SELECT * FROM PERSON");
This query should work fine.
Sorry I wasn't clear in my last posting. I meant to say: remove the square brackets in the column Name attribute and not remove the Identity property. The sentence should read:
"If your mapping file has the same issue, can you remove the square brackets from the column Name attribute and see if it solves your problem?"
So, in your mapping file, in the Person class, it should have the following column Name attribute for the _Id property, with out square brackets:
[Column(Name="_Id" <- No square brackets
I have got something
the bug IS NOT with the square brackets .....when I remove the identity field property on the linQ designer it works?
When you remove the identity field property, it should populate the objects properly; however, since it doesn't have an identity, you won't be able to perform CUD operations on it. With out the identity field, Linq to SQL will not have identity tracking enabled and it will end up being a read-only table.
To add:
Thank you for reporting this issue. From your issue we were able reproduce the issue and the product team is looking into an appropriate triage and resolution.
Thanks,
Kathy
thanks for the feedback....
