ADO.NET Entity Framework and LINQ to Entities Forum

A dependent property in a ReferentialConstraint is mapped to a storage-generated column. Column: 'ID'.

Wed, 17 Jun 2009 12:46:54 Z

Hi everyone! I searched the net, but couldn`t find the answer.

I have two tables: Content with primary key ID, which is bigint and identity

and second table Topic which has some other fields and is linked to Content by ID.

Content [ID, ...] Primary key, bigint, identity

Topic [ID, ...] primary key, bigint, foreign key from Content.ID

Topic inherits Content, so Content is the base table and Topic is dependent.

I generate an EDM model and can not insert any data into database because on SaveChages I get

"A dependent property in a ReferentialConstraint is mapped to a storage-generated column. Column: 'ID'."

As I understand this means, that when entity inserts data into Content it gets the database generated value of ID, but it can not use this value to immediately insert it into Topic.

What should I do? Are there any workarounds?

I know I can turn off identity in Content and set ID manually, but that will give me many problems with generating unique keys.

Also I considered using Guid as primary key, but values from this field will be exposed to users through URL and I want to make clean looking URLs

How to set Guid from EF

Wed, 25 Nov 2009 10:14:29 Z

Hi

I am new to EF. I have a DB with multiple tables, all tables have ID col as PK (auto increment witch works without a problem) rowguid col and date modifies col. the col have defaults in db as newid() (rowguid) and getdate() (modified date). This works fine when I add a row in SQL Management studio but what I wonder is how do I get this to work trough EF.

I have a small table with 4 col. CustomerID (PK)(int), CustomerName(nvarchar), rowguid(uniqueidentifier) and modifieddate(datetime). Now when I want to add a new Customer i run this.

            var customer = new Customer

            {

                CustomerName = "New Customer3",

                ModifiedDate = DateTime.Now

            };

this adds a new customer in my db with a new ID, Name = "New customer3" and modified date = DateTime.Now but my rowguid is 00000000-0000-0000-0000-000000000000 ...I have tried to add RowGuid = new Guid() but the result is the same.

Any idea??

Thanks

Entity inheritance makes big SQLs

Wed, 25 Nov 2009 09:25:17 Z

We are developing an application with a base entity with more than 10 childs (which inherited from it).

When we make any request with Linq to the base entity we get a SQL statement with a "UNION ALL" for each child. To make a Count() over the base entity it takes near one second and getting only one row can takes two seconds.

For this code:

public bool Exists(int appId, string loginName, DateTime userRegDate, long ahsayId)
        {
            var backupsets = from backupset in _entities.AhsayBackupSets
                             where
                                backupset.User.Appliance.Id == appId &&
                                backupset.User.LoginName == loginName &&
                                backupset.User.RegistrationDate == userRegDate &&
                                backupset.AhsayId == ahsayId
                             select backupset;
            return backupsets.Count() > 0;
        }

We get this SQL sentence:

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
	COUNT(1) AS [A1]
	FROM    [dbo].[AhsayBackupSets] AS [Extent1]
	LEFT OUTER JOIN  (SELECT 
		[UnionAll9].[C1] AS [C1]
		FROM  (SELECT 
			[UnionAll8].[C1] AS [C1]
			FROM  (SELECT 
				[UnionAll7].[C1] AS [C1]
				FROM  (SELECT 
					[UnionAll6].[C1] AS [C1]
					FROM  (SELECT 
						[UnionAll5].[C1] AS [C1]
						FROM  (SELECT 
							[UnionAll4].[C1] AS [C1]
							FROM  (SELECT 
								[UnionAll3].[C1] AS [C1]
								FROM  (SELECT 
									[UnionAll2].[C1] AS [C1]
									FROM  (SELECT 
										[UnionAll1].[Id] AS [C1]
										FROM  (SELECT 
											[Extent2].[Id] AS [Id]
											FROM [dbo].[AhsayOracleBackupSets] AS [Extent2]
										UNION ALL
											SELECT 
											[Extent3].[Id] AS [Id]
											FROM [dbo].[AhsaySystemStateBackupSets] AS [Extent3]) AS [UnionAll1]
									UNION ALL
										SELECT 
										[Extent4].[Id] AS [Id]
										FROM [dbo].[AhsayMysqlBackupSets] AS [Extent4]) AS [UnionAll2]
								UNION ALL
									SELECT 
									[Extent5].[Id] AS [Id]
									FROM [dbo].[AhsayMssqlBackupSets] AS [Extent5]) AS [UnionAll3]
							UNION ALL
								SELECT 
								[Extent6].[Id] AS [Id]
								FROM [dbo].[AhsayFileBackupSets] AS [Extent6]) AS [UnionAll4]
						UNION ALL
							SELECT 
							[Extent7].[Id] AS [Id]
							FROM [dbo].[AhsayExchangeServerBackupSets] AS [Extent7]) AS [UnionAll5]
					UNION ALL
						SELECT 
						[Extent8].[Id] AS [Id]
						FROM [dbo].[AhsayDominoBackupSets] AS [Extent8]) AS [UnionAll6]
				UNION ALL
					SELECT 
					[Extent9].[Id] AS [Id]
					FROM [dbo].[AhsayNotesBackupSets] AS [Extent9]) AS [UnionAll7]
			UNION ALL
				SELECT 
				[Extent10].[Id] AS [Id]
				FROM [dbo].[AhsayShadowProtectBackupSets] AS [Extent10]) AS [UnionAll8]
		UNION ALL
			SELECT 
			[Extent11].[Id] AS [Id]
			FROM [dbo].[AhsayWindowsSystemBackupSets] AS [Extent11]) AS [UnionAll9]
	UNION ALL
		SELECT 
		[Extent12].[Id] AS [Id]
		FROM [dbo].[AhsayExchangeMailBackupSets] AS [Extent12]) AS [UnionAll10] ON [Extent1].[Id] = [UnionAll10].[C1]
	LEFT OUTER JOIN [dbo].[AhsayUsers] AS [Extent13] ON [Extent1].[AhsayUserId] = [Extent13].[Id]
	INNER JOIN [dbo].[AhsayUsers] AS [Extent14] ON [Extent1].[AhsayUserId] = [Extent14].[Id]
	WHERE ([Extent13].[ApplianceId] = @p__linq__0) AND ([Extent13].[LoginName] = @p__linq__1) AND ([Extent14].[RegistrationDate] = @p__linq__2) AND ([Extent1].[AhsayId] = @p__linq__3)
)  AS [GroupBy1]',N'@p__linq__0 int,@p__linq__1 nvarchar(4000),@p__linq__2 datetime,@p__linq__3 bigint',@p__linq__0=2,@p__linq__1=N'antonio',@p__linq__2='2009-10-22 18:07:17',@p__linq__3=1256305376226

Is there some way to reduce the SQL overhead? We know we can use stored procedures for heavy sentences but we don't want to lose the Linq flexibility.

Thanks in advance.

Error 3034: An entity from one EntitySet is mapped to a row that is also mapped to an entity from another EntitySet

Tue, 26 Aug 2008 06:42:01 Z

Hi,

I've wasted all afternoon attempting to get the entity designer doing what I expect. I have added a new inheritance level on most of my objects. Is very frustrating that every time you change a classes inheritance, the designer loses all that classes relationships.

Eventually I thought I had it right, only to find a new problem which I haven't yet found a solution for. I get this error when I build:

Error 3034: Problem in Mapping Fragments starting at lines 2495, 2504: An entity from one EntitySet is mapped to a row that is also mapped to an entity from another EntitySet with possibly different key. Ensure these two mapping fragments do not map two unrelated EntitySets to two overlapping groups of rows.

The code it is referring to is:

Code Snippet

</EndProperty>

</EndProperty>

</AssociationSetMapping>

</EndProperty>

</EndProperty>

</AssociationSetMapping>

And I have several similar associations with exactly the same problem, even though this problem did not exist even after changing the inheritance of that particular object.

TourObjectID is the primary key defined in the parent class PictureID being the primary key in this particular class.

Unfortunately I have been unable to come up with a solution to this particular problem, so I hope someone can point me in a direction, so I can actually work on my project again.

...Stefan

"The underlying provider failed on Open." when invoking the SaveChanges method

Sun, 24 Aug 2008 13:51:15 Z

I wanna write a demo about the ado.net entity framework. But i find a strange problem whenever i invoke the saveChanges method.

            using (NorthwindEntities myDb = new NorthwindEntities())
            {
                Customers customer = new Customers();

Random rm = new Random();

                customer.CustomerID = "A" + rm.Next(9999).ToString();
                customer.CompanyName = "EntLib.com Forum";
                customer.Address = "http://www.EntLib.com";

myDb.AddToCustomers(customer);

int count = myDb.SaveChanges();

txtCustomerID.Text = customer.CustomerID;
}

i just map the Northwind db and insert a recond into the customers table. However, the error "The underlying provider failed on Open." is thrown. Can anyone help me resolve it? The following is the connectstring.

Update Model From Database Problem

Tue, 17 Nov 2009 17:45:57 Z

For a while I have been suspecting that "Update Model from Database" feature in edmx diagram is not working as I expected. So, I decided to test it. I created a simple database with a single table from which I created the edmx file. Then I added a column to the table and used "Update Model from Database" to reflect the change in the model. That step worked as expected and the model showed the additional column. Then I went back to the database and removed that column and tried again to Update the Model using the same steps as before. This time the model did not reflected the change. I tried different things to force the change but nothing seems to be working.
Finally I tried a drastic measure and deleted the edmx file and rectreated it from scratch. during this step I discovered another issue. I could not reuse the same entity name because the system complained that the name is already in use. This issue represent a compound problem, since now all code refrencing the entity context need to be renamed. Not sure if this issue is a bug or by design. If anyone has an explaination I would greatly appriciate it. The steps I took above are pretty easy to recreate.
Thanks,
Zev

Bulk Insert into table - How to manage the transaction?

Wed, 25 Nov 2009 05:23:35 Z

My scenario as follow,
1. Thomas is a Ticket Agent for GSC Bus
2. As Ticket Agent, he have a Prepaid.
3. Let's say, his balance is $125.45
4. Now, he perform purchasing 12 Ticket -- This process only click 1 submit button on front-end, front-end code will doing a looping to hit Stored Procedure
4. Total Amount for 12 Ticket is $130.45 -- at this level, he exceed his prepaid

My question is,
1. How transaction can manage the Atomicity for this Bulk Insert (12 insert into the table)?
2. Where to check the Total of Ticket Amount?
3. Did i need to check the sum of the 12 ticket <= $125.45 at front-end first, then perform the transaction? or check the sum of the 12 ticket <= $125.45 at Back-end (Stored Procedure), then perform the transaction?

Left Join for a 1:0:1 relation

Tue, 24 Nov 2009 17:56:56 Z

Hi folks,

I have three tables: Contact (C), ContactAddress (CA) and ContactAddressCategory (CAC). These relate to each other as 1 C : 0..n CA 1..1 CAC .
That is, a contact can have 0 to n addresses (i.e. street, city, etc.). Each address is linked to an address-category (i.e. default address, shipping address, etc.). CAC also has a boolean flag that identifies exactly what address category is the default one.

In my WPF-app, I have a window that allows for editing Contacts . There, a ComboBox (let's call it cboSearch ) displays all Contacts , allowing users to pick the one to be edited. In cboSearch , I'd like to display the contact's name along with the first address, if there is any (i.e. if there is a default-category address, use that; otherwise take any other one, or show an empty string if there is none at all).
That said, the data being returned is also being used as the "DataSource" for the window itself.

I'd appreciate someone giving me a little help on how I'd have to construct the LINQ-query (VB preferred, C# would also do though) - I'm just not getting there ...

Cheers,
Olaf

Entity 4 XML Mapping ?

Wed, 25 Nov 2009 01:48:34 Z

I'm interest on the CodeOnly feature on the Entity Framework 4, and want to know if there is a way to map the classes by a separated xml file like MappingSource on DLinq, Nhibernate, ...

Tks

Michel Haddad Doumit

Best Way to Check For Existence of a Relationship Performance-wise

Tue, 24 Nov 2009 23:24:34 Z

Hello,

We often want to check that an existing reference (an EntityReference<>) relationship exists. But I want to see if that relationship exists without having to load the record first. So consider I queried a LINQ to Entities object from the database... this object (Customer) has a relationship to Region (setup as an EntityReference<> so there is a Region and a RegionReference properties).

What is the best way to check a relationship existence... will the Entity key always be null in this case, or will the entity key have no related values? I am thinking of performance; if I don't have to load a relationship, I don't want to have to. That's what's nice about LINQ to SQL, the foreign or primary key is right in the record itself...

Thanks.

http://linkedin.com/in/brianmains

Help with Error 3034 in Entity Framework

Tue, 24 Nov 2009 21:16:56 Z

Hello,

I'm having serious trouble with error 3034 and i really tried to fix it looking at previous posts but I couldn´t make it.

I need someone who can help me quickly, because this is a job for college and I have only 1 week left!! This is the last thing to finish it!

Thank you very much for you help!

Guillermo Kreer

These are the errors I have (Sorry but my Visual Studio is in spanish) :

Error 9 Error 3034: Hay un problema en los fragmentos de asignación con inicio en las líneas 275, 284: Dos entidades con claves diferentes están asignadas a la misma fila. Asegúrese de que los dos fragmentos de la asignación no asignan dos grupos de entidades con claves solapadas al mismo grupo de filas.
C:\Users\Guille\Desktop\DA2Obligatorio2\DominioEF\HotelBD.edmx 276
Error 10 Error 3034: Hay un problema en los fragmentos de asignación con inicio en las líneas 275, 287: Dos entidades con claves diferentes están asignadas a la misma fila. Asegúrese de que los dos fragmentos de la asignación no asignan dos grupos de entidades con claves solapadas al mismo grupo de filas.
C:\Users\Guille\Desktop\DA2Obligatorio2\DominioEF\HotelBD.edmx 276
Error 11 Error 3034: Hay un problema en los fragmentos de asignación con inicio en las líneas 284, 289: Dos entidades con claves diferentes están asignadas a la misma fila. Asegúrese de que los dos fragmentos de la asignación no asignan dos grupos de entidades con claves solapadas al mismo grupo de filas.
C:\Users\Guille\Desktop\DA2Obligatorio2\DominioEF\HotelBD.edmx 290
Error 12 Error 3034: Hay un problema en los fragmentos de asignación con inicio en las líneas 287, 289: Dos entidades con claves diferentes están asignadas a la misma fila. Asegúrese de que los dos fragmentos de la asignación no asignan dos grupos de entidades con claves solapadas al mismo grupo de filas.
C:\Users\Guille\Desktop\DA2Obligatorio2\DominioEF\HotelBD.edmx 290
Error 13 Error 3034: Hay un problema en los fragmentos de asignación con inicio en las líneas 284, 298: Dos entidades con claves diferentes están asignadas a la misma fila. Asegúrese de que los dos fragmentos de la asignación no asignan dos grupos de entidades con claves solapadas al mismo grupo de filas.
C:\Users\Guille\Desktop\DA2Obligatorio2\DominioEF\HotelBD.edmx 299
Error 14 Error 3034: Hay un problema en los fragmentos de asignación con inicio en las líneas 287, 298: Dos entidades con claves diferentes están asignadas a la misma fila. Asegúrese de que los dos fragmentos de la asignación no asignan dos grupos de entidades con claves solapadas al mismo grupo de filas.
C:\Users\Guille\Desktop\DA2Obligatorio2\DominioEF\HotelBD.edmx 299

This is the XML snippet:

<EntitySetMapping Name="Reserva">
            <EntityTypeMapping TypeName="IsTypeOf(HotelBDModel.Reserva)">
              <MappingFragment StoreEntitySet="Reserva">
                <ScalarProperty Name="checkout" ColumnName="checkout" />
                <ScalarProperty Name="idReserva" ColumnName="idReserva" />
                <ScalarProperty Name="fechaIngreso" ColumnName="fechaIngreso" />
                <ScalarProperty Name="fechaSalida" ColumnName="fechaSalida" />
                <ScalarProperty Name="ocupada" ColumnName="ocupada" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="HotelBDModel.ReservaFactura">
              <MappingFragment StoreEntitySet="Reserva">
                <ScalarProperty Name="idReserva" ColumnName="idReserva" /></MappingFragment></EntityTypeMapping>
            <EntityTypeMapping TypeName="HotelBDModel.ReservaFacturaWeb">
              <MappingFragment StoreEntitySet="Reserva">
                <ScalarProperty Name="idReserva" ColumnName="idReserva" /></MappingFragment></EntityTypeMapping></EntitySetMapping>
          <AssociationSetMapping Name="FK_Reserva_Cliente" TypeName="HotelBDModel.FK_Reserva_Cliente" StoreEntitySet="Reserva">
            <EndProperty Name="Cliente">
              <ScalarProperty Name="idCliente" ColumnName="nroCliente" />
     </EndProperty>
     <EndProperty Name="Reserva">
              <ScalarProperty Name="idReserva" ColumnName="idReserva" />
            </EndProperty>

Entity Framework + Windows Forms provide a bad software architecture base ?

Tue, 24 Nov 2009 20:39:43 Z

Hello,

I do not like Entity Framework together with Windows Forms (Data Binding). Both together provide a bad software architecture, why? I am gonna show here:

Lets assume I want to add an Entity called Customer to my database. I use MVP design pattern for Windows Forms apps.

I can add this entity in 2 ways:

In my View/Form class:

bindingSourceCustomer.DataSource = queryCustomers;

Customer customer = new Customer();
                 customer.FirstName = "Lisa";
                 customer.LastName = "Tatum";

   Way 1.) bindingSourceCustomer.Add(customer); databaseContext.SaveChanges();

               -> BAD , because the model/entity Customer is instantiated in the VIEW what has nothing to do there.

   Way 2.) presenter.AddCustomer();
               especially in PRESENTER class:

               public void AddCustomer()
        {
                    Customer customer = new Customer();
                    customer.FirstName = view.FirstName;
                    customer.LastName = view.LastName;

                    databaseContext.AddObject("Customer", customer);
                    databaseContext.SaveChanges();

                    var queryCustomer = databaseContext.Customer.Execute(MergeOption.AppendOnly);

                    view.RebindNewCustomers(queryCustomer);
          }

VIEW:

public void RebindNewCustomers(ObjectResult<Customer> queryCustomer)
{
     bindingSourceCustomer.DataSource = queryCustomer;
}

         -> BAD , because for every entity change in the GUI I have to make an extra roundtrip to the database and query it for the new added customer.

             What a waste of ressource, impossible to use in a client/server application.

Let me know your thoughts, please!

make a scalar property a unique field ?

Sat, 21 Nov 2009 11:03:10 Z

Hello,

Event:

eventID int identity (PK)
eventname varchar (Unique)

How can I set my eventname field as a unique field so inserting of 2x "party" is not possible.

Haven`t found anything in Visual Studio ??

Unusual exception working with Entity Framework in Multithreding environment

Fri, 20 Nov 2009 05:23:19 Z

I have a database model which I have imported in my code using Entity Framework(EF). There are some entities that are created by EF. I retrieve x no of entities using Function import and then my business logic works directly on those retrieved entities and updates certain properties based on some logic. what I am doing is, for each entity i am creating a new thread and applying my business logic on them using an action chain that I have built. So, there is one entity passed to a thread and all of them are being processed asynchronously. The problem now is that I keep on getting this weird exception every now and then (the exception is not consistent, it comes from any action in the action chain for any property), "EntityMemberChanged or EntityComplexMemberChanged was called without first calling EntityMemberChanging or EntityComplexMemberChanging on the same change tracker with the same property name. For information about properly reporting changes, see the Entity Framework documentation".

To prevent this I created partial class for the entities and created wrapper properties over my entity properties and in the set of my wrapper properties, I am setting the main entity property inside a lock so that if it is because of multi threading situation, that gets synchronized, but I am still getting that exception. Can anybody be of help in this?. I have tried so many things but nothing is actually working for me. Please guide me if I am doing something wrong.

The other thing that may be of importance is that my entity has many Nullable properties but the wrapper properties in my other partial class are not Nullable and I am setting these properties inside the lock.

To query directly the Storage Model using EF 4.0

Tue, 24 Nov 2009 11:01:54 Z

Can i get the column names of my table which are not mapped with my Entity in EF 4.0.

for Example

if i have a Table GL_Book( ID, Name , Description ,Cost)

and in the Conceptual model i have mapped it to Book(Name, Description).

Can i access the remaining columns of my GL_Book(say cost) table using entity framework in my code(by querying the storage model)?

i know using the "metadataworkspace" i can query my entity but can we use this to query the storage model.
if yes can get the columns of my table which is extensible and my clients have extended them at Run time and i want to access their values at the runtime.

Thanks & Regards

Satender Kumar

Invalid FORMATETC Structure

Sun, 04 Mar 2007 14:32:20 Z

Hi,

I'm getting this error: Invalid FORMATETC structure (Exception from HRESULT: 0x80040064 (DV_E_FORMATETC)) when creating a mapfile from existing data store. This is done in a dll. Using the wizard from a console application project works fine though.

Entity Framework & Transaction Scope usage

Mon, 23 Nov 2009 18:34:37 Z

I am having an issue using Transaction Scope with Entity Framework.
I get an exception : MSDTC on server '<Server Name>' is unavailable.
As per the suggestion from this article : http://msdn.microsoft.com/en-us/library/bb738698.aspx
I have the following piece of code to prevent escalating the transactions by MSDTC

using (TransactionScope ts = new TransactionScope())
{
context.Connection.Open();

//Add Operation : context.SaveChanges();

//Select from a table

Table t = context.Table.where (o => o.ID == someID).First(); //Getting the above exception here

//Add Operation : context.SaveChanges();

ts.Complete();

}

catch (Exception ex)

{

//Handle Exception

}

finally
{
      if (context.Connection.State == ConnectionState.Open)
      {
           context.Connection.Close();
      }
}

I have MSDTC service stopped on my machine.

If I start it, (Run-> dcomcnfg), then it works fine.

Why is it throwing the exception when I turn off MSDTC?

I am using a single connection string and context. I just want to make sure that thry don't unnecessarily get escalated to distributed transactions.

Please advice..

Thanks!

Getting SqlException "Invalid column name 'User_Id' from EF4 code-only

Tue, 24 Nov 2009 13:27:04 Z

I got a strange behaviour with code only (EF CTP) where I get an EntityCommandExecutionException, and the inner exception is a SQLException saying: Invalid column name 'User_Id'.

The strange thing with this is that my POCO does not contain a User_Id (there is even no such field/property in my whole solution).

Here is the stack trace from the EntityCommandExecutionException:

   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Repositories.EF.EFRepository`2.GetAll() in C:\Projects\Repositories.EF\EFRepository.vb:line 64
   at epositories.Website.EFBlogAggregateRepository.GetAllBlogs() in C:\Projects\Repositories.Website\EntityFramework\Repositories\AggregateRepositories\EFBlogAggregateRepository.vb:line 18
   at Repositories.Test.BlogTest.GetAllBlogs() in C:\Projects\Repositories.Test\BlogTest.vb:line 70

Does anybody have an idea what is going on here?

EF: How to avoid string truncation sql error

Fri, 20 Nov 2009 12:37:28 Z

Hi,

I bet you were going to write "Make sure the string isnt too long" :)
But isn't there some way to let EF truncate strings?

--

Default Values

Wed, 18 Nov 2009 16:36:57 Z

Hi,

I'm new to EF and just playing around with it currently. I'm experimenting with creating a WCF web service API using EF to query the database and provide data classes for the client in a shared interface dll. I'd done this in LINQ to SQL and it worked well but due to recent news wanted to see how well it would port to EF.

I want the client to be able to instantiate an EF generated class from the shared interface dll so that he can pass it to my web method as a set of query parameters. E.g. if the "Name" property has a value, add it to the where clause on the server side etc.

Problem is the generated classes seem to constrain on Not Null fields but don't make use of the default value that was set in the database. This means when the client instantiates the object with just one or two fields, EF complains that all the other fields can't be left as null. Does this mean the client has to fill in every single "not null" field just when he wants to query on one thing?

If this is a limitation in using EF then the alternative would be to create a set of classes that mirrors the generated ones, specifically for creating query parameters! That seems pointless when you already have the classes made for you. Is there a better way? Or should I just go back to LINQ to SQL?

ADO.NET entity framework from MS-ACCESS database

Fri, 20 Nov 2009 10:14:37 Z

Please can u guide me how to connect ms-access.mdb file through ado.net entity model using vs2008.
I am already installed vs2008sp1 and .net3.5sp1.

Navigation properties are null on Multiple Entitity set

Sun, 22 Nov 2009 11:28:11 Z

Greetings,

we imported from the db a table in one entity. since such table is very big, we copied and pasted the entity to split properties into several entities. We then added associations to the model (1 to 1) to relate all our entities. The model is working great (update, dbload), except for added entities: navigational properties are always null.

MainEntity
Id
Prop1
Prop2
Prop3
Prop4
Prop5

We copy and pasted the entity and removed Prop4 and Prop5 from MainEntity, and Prop1, Prop2, and Prop3 from new entity. Added association.

so we have the following situation:

MainEntity
Id
Prop1
Prop2
Prop3
Details ---> Navigation property

MainEntityDetails
Id
Prop4
Prop5
MainEntity ---> Navigation property

With such model loading and lazy loading from database works correctly. What does not work out of the box is the insertion of new entities:

MainEntity e=context.MainEntitySet.CreateObject();

e.ID = 0;

context.MainEntitySet.AddObject(e);

Unfortunately e.Details is always null.

What's wrong?

ID collision with table-per-concrete-type

Sun, 22 Nov 2009 05:37:08 Z

Hey all,

I have an abstract base class EntityBase which does not map to a table and a few derived classes like Empire, City, Building which each map to their own tables. Each table has an Id field, which on the database is set as an AUTOINCREMENT primary key column.

My problem occurs when trying to add a new Building to the EDM object context, it throws an exception while trying to add the new object due to an ID collision in the City table. The very next ID to be used in Building is 10, which happens to already be occupied by an existing City row.

Is there a way around this? I don't mind if each table has a different ID space, but EDM seems to take issue with having the same key within the same EntitySet. Is the proper fix to define separate EntitySets for each concrete type? How does that work? Or is that just a more problematic approach...

ADO.NET Useless Model-Defined Functions

Sat, 21 Nov 2009 19:51:50 Z

Hello,

I have this small complain about Entity Framework 4.0 which I would like to throw in. I have an entity A, which has A.Value1 and A.Value2. These values are in a table - I create the model, everything fine there.

Now I need to sum up the two values. And I want to bind to that sum. So I extend my entity A in a partial class and add a new property, A.Sum, which sums Value1 and Value2.

Next, I want to reflect changes made to Value1 and Value2 in the sum, to notify the interface of value changes so the sum gets recalculated - so here I run into the first problem. I have to make A.Value1 fire the change of A.Sum also. Can't do that, so I make a new property, A.Value1FireSum, which basically copies A.Value1 and adds fire change to A.Sum. Can't do that, because I get an exception saying that A.Sum is not a mapped property in the database.

So I throw away all this entity extension thing and look into Model-Defined Functions which are said to do just that - add custom abilities to entities. I don't use function import because all I want is a simple sum, so I define the function in XML, in the model, according to MSDN samples. All fine there, but next I find out you actually have to also write the function definition in code, because it's not automatically inferred. I do that, but then again it won't work because it only works using linq syntax.

Guys. I need a simple, basic, idiot sum, which would get updated on property changes of other values. I know I can use POCO and basically rewrite the whole Entity Framework myself in the process - but this is the most simple scenario. It's so basic I still struggle to understand why it has not been implemented in a more accessible manner. Is it so hard to have a simple extension and NOT use POCO? Simply extend the generated entity and be able to fire property changes for the extending properties?

how to insert into a table which has 1-many with nullable value

Thu, 19 Nov 2009 20:43:15 Z

I am trying to insert a record into a table (says Preferences) which has a column name ColorId, FK to a table (says Color) and it can be nullable. It's a 1-many between Color and Perferences. But I am not sure the EF allows null value on the Many End Property and if so, I do not know how to handle it.

this is the CreatePreferences method i have overridden.

public static Preferences CreatePreferences(string name, int colorId)

{

Preferences p = new Preferences();

p.Name = name;

if ( colorId > 0 )

{

//this is for the case the Color column is not null. This case is working fine

p.ColorReference.EntityKey = new EntityKey("ColorQualifiedEntitySet","EntityKeyName",colorId);

}

else

{

//this is the case of the color column is null. NOT WORKING.

p.ColorReference.EntityKey = new EntityKey("ColorQualifiedEntitySet","EntityKeyName",null);

}

Please help me how to insert it. Thanks in advance.

Reference.EntityKey

Fri, 20 Nov 2009 23:19:11 Z

I have a Patient table
I have a Test table
I have a TestResult table that has references to the 1st 2 tables.

I try to loop thru all the tests that were done on that patient:
TestResult result = new TestResult();
foreach (test selected in my grid)
{
result.PatientReference.EntityKey = new EntityKey("MyEntities.Patient", "PatientId", patientId);
result.TestTypeReference.EntityKey = new EntityKey("MyEntities.TestType", "TestTypeId", testTypeId);
}
context.AddToResult(result);
context.SaveChanges();

Which works fine the first time thru the loop, however on the 2nd pass, I guess the EntityKey on the Patient already exists so what is the correct way to write this?

ASP.NET Web Form slow on first request using Entities

Mon, 23 Nov 2009 23:34:35 Z

I have a web forms page that uses an ADO.NET Entity Model to get some data from the database using a LINQ query and builds the HTML to display in code behind C#.

The problem is, the first page request after more than say 30 minutes, takes about 10 seconds to render. After that, each page renders in less than a second.

My guess is the database connection has to be reopened however, before I started troubleshooting this one, I thought I'd ask the group for some ponters giving that I'm new to Entities.

TIA,
George

The primitive type 'Edm.Int32' could not be mapped within the current metadata workspace and provider., near type identifier, line 7, column 106.

Fri, 20 Nov 2009 02:13:20 Z

I am binding an EntityData Source and one of the parameters is based on the value from a dropdown. The value of the ddl was originally an int however based on a particular use case I had to append a letter to the front of it making it a string. My problem is that I can't seem to get the entity sql correct. Below is the markupcode:

asp:EntityDataSource ID="UnassignedCrewLeadsDataSource" runat="server" AutoGenerateWhereClause="True"

CommandText="SELECT value crew

FROM VendorCrew AS crew

WHERE NOT EXISTS(SELECT sfvc.VendorCrew.VendorCrewId

FROM ShipmentForwardingVendorCrew AS sfvc

WHERE sfvc.Shipments.ShipmentId = @ShipmentId

and sfvc.VendorCrew.VendorCrewId = crew.VendorCrewId)

AND (crew.CompanyEntities.CompanyEntityId = cast(replace(@VendorId, 'O', '') as Edm.Int32)

OR crew.CompanyEntities.CompanyEntityId = cast(replace(@VendorId, 'D', '') as Edm.Int32))

AND crew.IsActive = true

AND crew.IsCrewLead = true"

ConnectionString="name=RITSNetEntities" DefaultContainerName="RITSNetEntities"

Include="ShipmentForwardingVendorCrew,CompanyEntities">

<asp:QueryStringParameter Name="ShipmentId" QueryStringField="10" Type="Int32" />

<asp:ControlParameter ControlID="ddlODAgent" Name="VendorId" PropertyName="SelectedValue"

Type="String" />

</CommandParameters>

</asp:EntityDataSource>

Adam

create new table runtime

Mon, 23 Nov 2009 20:10:42 Z

Hello,

I would like to know if its possible to create new table dynamically from the program. And then when new one is created based on known schema list or tables from the database with the same schema and choose one as a linq mapped object.

thanks for help

FunctionImport and Reference Properties

Wed, 18 Nov 2009 09:12:56 Z

I'm loading a set of a entites using a stored procedure.
Now the loaded entites have the navigation property set to null and would like to assign the manually in this way :

 List<Assignment> assignments =
                _model.AssignmentsByProjectId(ProjectId).ToList();


            foreach (Assignment a in assignments)
                a.Resource = _model.Resource.First(e => e.ResourceId == Convert.ToInt32(a.ResourceReference.EntityKey.EntityKeyValues[0].Value));

The problem is that event he ResourceReference property is null, so i have no Foreign Key reference...
Any Help ?! THX

How to separate generated edmx entities from Context methods in different projects?

Sun, 25 Oct 2009 17:12:09 Z

Hi,

In my architecture (VS2008, Asp.Net 3.5 project, wcf project, and some dao project, dto, common libraries), i'd like to separate edmx generated class.

There's no site that could solve my issue, solutions iv've found is implementing POCO objects and mapper. I'd like The VS2008 edmx code generator to put the DTO in on common project and the DAO in an other one, not referenced in the UI project but referenced in my WCF services project.

My goal is implementing a dynamic proxy in the UI layer with unity container -> I need to externalise services contracts and entities definitions in a common project-> but does not seem to be possible with edmx file as is.

Is that possible and How?

Thanx'

WHERE IN clause?

Fri, 11 Jan 2008 23:39:03 Z

Hi, we just started experimenting with LINQ to Entities and we're looking to be able to accomplish the same functionality as a WHERE IN statement in SQL because we have a single field that we want to compare to multiple values. For example:

Code Block

SELECT *

FROM Foo

WHERE blah IN (1, 3, 5, 7)

Currently the list of values we wanted to compare to is in a List<T>. We tried the following LINQ query, but get an error:

Code Block

List<int> statusesToFind = ...;

var foos = from foo in myEntities.Foos

join status in (from statusToFind in statusesToFind

select statusToFind) on foo.Status equals status

select foo;

The error we received was:

"Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types (for instance Int32, String and Guid) are supported in this context."

We also tried this query:

Code Block

List<int> statusesToFind = ...;

var foos = from foo in myEntities.Foos

where statusesToFind.Contains(foo.Status)

select foo;

This version seems more along the lines of what we're trying to say literally, but it complains about not being able to translate the Contains method to something usable in the query, which I suppose makes sense.

Anyway, does anyone have any hints on how to accomplish the equivalent of a WHERE IN?

TIA,

Amit

I am getting the error ????Please help

Mon, 23 Nov 2009 11:50:39 Z

hello all,i am getting an error in my mvc application please help me.

The error I am getting is following:

The entity or complex type 'Northwind Model.Products' cannot be constructed in a LINQ to Entities query.

i am getting this error in my class file :
public IList<Products> fetchAllFeaturedProducts()
        {

            using (NorthwindEntities modelObject = new NorthwindEntities())
            {

                IQueryable<Products> currentFeaturedProducts = (from currentFeaturs in modelObject.Products
                                                                select new Products ()
                                                                {
                                                                    //image_name = currentFeaturs.image_name,
                                                                    ProductName = currentFeaturs.ProductName,
                                                                    UnitPrice = currentFeaturs.UnitPrice.Value
                                                                }

                                                                          ).AsQueryable ();

                return currentFeaturedProducts.ToList();
            }

        }
whichj i am calling in my controller class:

public ActionResult showFeaturedProducts(string sidx, string sord, int page, int rows)
        {
            return Content(JsonHelper.JsonForJqgrid(getFeaturedProducts(sidx, sord, page, rows), rows, 0, page), "application/json");
        }

        //code to fetch shopping cart with paging
        public DataTable getFeaturedProducts(string sidx, string sord, int page, int pageSize)
        {

            productrepository objProductRep = new productrepository();
            DataTable tblFeaturedProducts = new DataTable();
            DataRow catRow;
            //tblFeaturedProducts.Columns.Add("imagename", Type.GetType("System.String"));
            tblFeaturedProducts.Columns.Add("productName", Type.GetType("System.String"));
            tblFeaturedProducts.Columns.Add("productPrice", Type.GetType("System.String"));
            IList<Products> allFeaturedProducts = objProductRep.fetchAllFeaturedProducts();

            foreach (Products oneFeaturedProduct in allFeaturedProducts)
            {
                catRow = tblFeaturedProducts.NewRow();
                //catRow["imagename"] = oneFeaturedProduct.image_name.ToString();
                catRow["productName"] = oneFeaturedProduct.ProductName.ToString();
                catRow["productPrice"] = Convert.ToDouble(oneFeaturedProduct.UnitPrice.ToString());
                tblFeaturedProducts.Rows.Add(catRow);

            }
            return tblFeaturedProducts;
        }

and this I am passing to the jquery grid
jQuery(document).ready(function() {

    jQuery("#editgrid").jqGrid({
            url: '/Home/showFeaturedProducts/',
            datatype: 'json',
            mtype: 'GET',
            colNames: [ 'Name', "UnitPrice"],
            colModel: [
//          { name: 'Id', index: 'Id', width: 40, align: 'left' },
          { name: 'Name', index: 'Name', width: 40, align: 'left' },
//          { name: 'QuantityPerUnit', index: 'QuantityPerUnit', width: 200, align: 'left' },
          { name: 'UnitPrice', index: 'UnitPrice', width: 200, align: 'left'}],
            rowNum: 10,
            rowList: [5, 10, 20, 50],
            sortname: 'Id',
            sortorder: "desc",
            viewrecords: true,
            caption: 'My first grid'

        });
    });

Please help me what shall I do and I am using the Entity data model as the model of my database

Please help me

Thanks in advance
Riti

UpdateException (cardinality constraints) when adding to 1:0..1 relation

Fri, 20 Nov 2009 21:15:42 Z

I have the following simple model:
http://img504.imageshack.us/img504/2434/capturewn.png

I want to add a newly created single ObservedFileMeta and several newly created ObservedFileParts to an ObservedFile which is already in the database in this case.
Whjen I try to run the following code:

            Context.AddToObservedFileMetaSet(meta);
            Context.Attach(file);
            file.Meta = meta;

            foreach (ObservedFilePart part in parts)
            {
                Context.AddToObservedFilePartSet(part);
                file.Parts.Add(part);
            }

           Context.SaveChanges();

I get an UpdateException:

System.Data.UpdateException was unhandled
  Message="A relationship is being added or deleted from an AssociationSet 'ObservedFileObservedFileMeta'. With cardinality constraints, a corresponding 'ObservedFileMeta' must also be added or deleted."

While searching the web I found several people encountering a similiar problem but only when trying to delete entities.

I debugged to check if the EntityStates of the objects seem right:
file has "Unmodified" while meta and the parts have "Added" as their EntityState.

What am I doing wrong or need to make sure?

Thank you for your help.

Pick-list: relationship not mapped correctly?

Mon, 23 Nov 2009 11:02:56 Z

Hi folks,

I have two tables, i.e.:

tbl_Contacts (mapped as entity "Contact")
tbl_Titles (mapped as entity "Title")

There is a 1:n relationship between Title and Contact . The PKs/FKs are GUIDs (RowGuid = True). That is, each Contact record needs to have a Title-reference (FK set).
When querying the DB for contacts, i.e. ...

Dim results = _
   From r In _db.Contacts _
   Order By r.LastName, r.FirstName _
   Select r

... the Title-property for the contact-record will always be empty (null/nothing). If I change the above to ...

Dim results = _
   From r In _db.Contacts.Include("Title") _
   Order By r.LastName, r.FirstName _
   Select r

... the Title-property will be available. Julia Lerman's "Programming Entity Framework" tells me that, actually, the first query should return what the second does.

What am I doing wrong here?

Cheers,
Olaf

Another Question About Mapping Entities to VIEWS

Tue, 17 Nov 2009 21:31:28 Z

We ran into the issue today with using VIEWS to map your entities to (All Non Nullable Columns are assumed to be part of the key). For various reasons (of which I dont care to get into), we need to use Views as opposed to mapping directly to tables. Is there any update from MSFT that will allow us to map to views as if they were tables? If not then I would say this is a HUGE OVERSIGHT on Microsoft's part and it really makes the EF as an ORM worthless to us. I really need a solution quickly on this. Cheers, Greg Jackson Data Architect Ethics Point

Gregory A Jackson

Connectionstring??

Sat, 21 Nov 2009 23:28:19 Z

Hello,

I have the three, stand-alone medata files (DataModel.csdl, DataModel.msl, DataModel.ssdl) in a library assembly named 'DataModel' each files Build Action property set to Embedded Resource. I have a client project that references this assembly.

I've tried a million different App.Config connection strings, and I can't seem to load the metadata files. I either get an invalid path or an invalid assembly name exception.

I'm trying something along the lines of:

connectionString="Metadata=
         res://DataModel,1.0.0.0,neutral,null/DataModel.csdl|
         res://DataModel,1.0.0.0,neutral,null/DataModel.ssdl|
         res://DataModel,1.0.0.0,neutral,null/DataModel.msl;

This one gives me:

Exception: The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

The assembly DataModel.dll is in bin/debug in the clint app. I've tried a bunch of relative paths. When I llok at the assembly in reflector, the resources are named DataModel.DataModel.csdl, etc. so I tried that too.

Any help much appreciated.

Bob

At least one of the key properties of AssociationSet must be mapped to all the key properties error using one-to-many relationship with link table in Entity Framework

Mon, 16 Nov 2009 17:03:27 Z

Hi all, this is my first post so please be gentle...

I am working with the entity framework and just can't understand why I am getting the following error;

At least one of the key properties of AssociationSet ServiceOrder_TaxRate must be mapped to all the key properties (LinkTable_ServiceOrder_TaxRate.ServiceOrderID, LinkTable_ServiceOrder_TaxRate.TaxRateID) of table LinkTable_ServiceOrder_TaxRate.

My table 'LinkTable_ServiceOrder_TaxRate' is just a link table with two columns,
ServiceOrderID
TaxRateID

I created my entity diagram from the SQL DB and VisualStudio automatically created the associations and hid the link table.

As you would expect, the associations were created as many-to-many and if left in this state there are no errors.
But I want this association to me many-to-one (Many Service Orders can have one Tax Rate, One Tax Rate can have many ServiceOrders)

When I change the association with the tax rate end set to '1 (One)' the above error occurs.

Double clicking on the error takes me to the Mapping Details but the appear correct to me:
One association that maps to table LinkTable_ServiceOrder_TaxRate
SeriveOrder.ID -> ServiceOrderID
TaxRate.ID -> TaxRateID

Now, I know I could just add a column to the Service Order table to hold the FK of the requred Tax Rate, but for reporting and consistency I want to use seperate link tables for all of my associations.
Also I don't see why the above approach doesn't work.

Please could someone explain how to use LinkTables with associations other than many-to-many?

Thanks in advance!
Matt

How can I convert a type so I can compare two columns with different types in joins?

Sat, 14 Nov 2009 09:24:26 Z

I'm relatively new to Linq to Entity framework, and I'm not quite yet familiar with all the limitations and gotya's of LINQ to entity queries. The problem I'm having is this:

var variable = from A in TableA

                    join B in TableB on A.intType equals B.stringType

                    select A.someProperty;

Now, you can't use CLR's common type conversion methods like Int32.Parse(B.stringType) up there, because this is considered arbitrary client function and Linq to Entity does not know how to turn Int32.Parse function into stored expression. And it is not apparent to me how you can convert the string type to int for comparison above.

I've looked at some references to EntityFunctions and SQLfunctions but they don't seem to help in this situation.

EntityFramework POCO Code Generator

Mon, 23 Nov 2009 00:14:43 Z

Hello,

I'm playing around with some of the Feature CTP Walkthroughs from the ADO.NET team blog. I have VS Beta 2 and the Entity Framework Feature CTP 2 installed. I was working with the POCO Templates for Entity Framework walkthrough.

First, I see that this and the POCO Template walkthrough targets Beta 1 and CPT 1. I was trying to implement it with Beta 2, but the EntityFramework POCO Code Generator T4 template isn't listed in my project item templates. I only have the ADO.NET Self-Tracking Entity Generator template.

Was the POCO template removed fromm Beta2/CPT2??? Or should I have it somewhere??

Thanks for any help.

Bob