Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error. UpdateFromQuery with TPT. (Z.EntityFramework.Plus.EF6 (v. 1.8.11)) #426

Closed
cjmairair opened this issue Oct 25, 2018 · 20 comments
Closed

Error. UpdateFromQuery with TPT. (Z.EntityFramework.Plus.EF6 (v. 1.8.11)) #426

cjmairair opened this issue Oct 25, 2018 · 20 comments
Assignees
@JonathanMagnan

Comments

@cjmairair
Copy link

I have the following TPT setup.

    public abstract class AnimalBase
    {
        public virtual int Id { get; set; }
        public virtual int Age { get; set; }
        // ....
    }

    public class Animal : AnimalBase
    {
        // ...
    }

    public class Dog : Animal{ /* ... */}
    public class Cat: Animal{ /* ... */}

When I try to do something like this:

context.Animals.Where(...).Update(o => new Animal() { Age = age });

I get an exception from Z.EntityFramework.Plus.EF6 (v. 1.8.11).

System.Data.SqlClient.SqlException: Invalid column name 'Id'

(It seems like the exception happens before any SQL is even generated, because I ran SQL Profiler and didn't see anything. But that doesn't seem right, because the exception was a SqlException.)
@JonathanMagnan JonathanMagnan self-assigned this Oct 26, 2018
@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

Inheritance is currently not supported in EF Plus.

However, Entity Framework Extensions already support them.

You can find some more info about what might happen to the support for this here: #269 (comment)

Let me know if that answered your question.

Best Regards,

Jonathan

@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

This issue will be closed since it has been answered.

Feel free to reopen it if you feel otherwise.

Best Regards,

Jonathan

@cjmairair
Copy link
Author

cjmairair commented Nov 13, 2018 via email

@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

I will make my developer look at it later today,

If it works, we will provide an online example, otherwise, we will fix it.

Best Regards,

Jonathan

@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

It seems our library support UpdateFromQuery with your scenario: https://dotnetfiddle.net/Rea74P

But doesn't yet with DeleteFromQuery. If I remember well, it was intended at first to avoid some mistake but perhaps we should relook this decision.

Do you need the DeleteFromQuery do work as well?

Best Regards,

Jonathan

@Crossbow78
Copy link

Crossbow78 commented Nov 15, 2018 •
edited
Loading

I have a similar issue, but there's no inheritance involved. This is my model, generated from an edmx:

public class GroupMembership
{
	public int Id { get; set; }
	public int GroupId { get; set; }
	public int UserId { get; set; }
	public bool Changed { get; set; }
	public System.DateTime EntryStamp { get; set; }
	public Nullable<System.DateTime> ArchivedDate { get; set; }
}

Id is the primary key, and GroupId and UserId are foreign keys.

Using Entity Framework Extensions 3.6.11:

_db.GroupMemberships
	.Where(x => !x.ArchivedDate.HasValue && groupIds.Contains(x.GroupId))
	.UpdateFromQuery(x => new EF.Entities.GroupMembership { Changed = true });

this is the generated query (slightly reformatted) that I see in SQL Server Profiler:

MERGE INTO [dbo].[GroupMemberships] AS DestinationTable
USING
(
	SELECT 
		CAST([Extent1].[Id] AS int) AS [C1]
	FROM [dbo].[GroupMemberships] AS [Extent1]
	WHERE ([Extent1].[ArchivedDate] IS NULL)
		AND (CAST([Extent1].[GroupId] AS int) IN (1, 2))
		AND (CAST([Extent1].[GroupId] AS int) IS NOT NULL)
) AS StagingTable
ON 
WHEN MATCHED THEN
	UPDATE 
	SET [Changed] = 1;

which results in the following SqlException:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHEN'.

And indeed, there is no key selection to join the StagingTable with the DestinationTable.
It would work with this: ON [DestinationTable].[Id] = [StagingTable].[C1]

Using Entity Framework Plus 1.8.13:

_db.GroupMemberships
	.Where(x => !x.ArchivedDate.HasValue && groupIds.Contains(x.GroupId))
	.Update(x => new EF.Entities.GroupMembership { Changed = true });

this is the generated query (slightly reformatted) that I see on SQL Server Profiler:

UPDATE A 
SET A.[Changed] = 1
FROM [dbo].[GroupMemberships] AS A
INNER JOIN (
	SELECT 
		CAST([Extent1].[Id] AS int) AS [C1], 
		CAST([Extent1].[GroupId] AS int) AS [C2], 
		CAST([Extent1].[UserId] AS int) AS [C3], 
		[Extent1].[Changed] AS [Changed], 
		[Extent1].[EntryStamp] AS [EntryStamp], 
		[Extent1].[ArchivedDate] AS [ArchivedDate]
	FROM [dbo].[GroupMemberships] AS [Extent1]
	WHERE ([Extent1].[ArchivedDate] IS NULL)
		AND (CAST([Extent1].[GroupId] AS int) IN (1, 2))
		AND (CAST([Extent1].[GroupId] AS int) IS NOT NULL)
) AS B ON A.[Id] = B.[Id]

which results in the following error:
System.Data.SqlClient.SqlException: Invalid column name 'Id'

And indeed, there is a faulty key selection to join A with B.
It would work with this: AS B ON A.[Id] = B.[C1]

@JonathanMagnan
Copy link
Member

Hello @Crossbow78 ,

Could you create your own issue with that? It will help to better follow it.

It's easier when creating a new issue and referencing when needed a similar issue.

Best Regards,

Jonathan

@Crossbow78 Crossbow78 mentioned this issue Nov 15, 2018
Closed
@cjmairair
Copy link
Author

cjmairair commented Nov 15, 2018 via email

@JonathanMagnan
Copy link
Member

Great,

My developer already started to look at it this morning.

I will give you an update next Monday.

Best Regards,

Jonathan

@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

The v3.16.12 has been released,

We fixed some issue that was still unsupported in our library, could you try it and let me know if everything works as expected?

Best Regards,

Jonathan

@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

Did you have the chance to test the latest version?

Best Regards,

Jonathan

@cjmairair
Copy link
Author

cjmairair commented Oct 18, 2019 •
edited
Loading

It still doesn't work. I get the same error as originally.
Also, Update & UpdateFromQuery fail with the same error.
(But TPH works now.)

I know it's been a while. I just couldn't get around to testing this.

I'm using Z.EntityFramework.Plus.EF6 1.10.3.0 and Z.EntityFramework.Extensions 4.0.9.0.

@cjmairair
Copy link
Author

cjmairair commented Oct 18, 2019 via email

@JonathanMagnan
Copy link
Member

Hello @cjmairair ,

Let start by using the following Online Example: https://dotnetfiddle.net/Rea74P

Could you let me know what's not working? Can you reproduce the issue to give us some hint about where to start?

Best Regards,

Jonathan

@cjmairair
Copy link
Author

cjmairair commented Oct 18, 2019 via email

@cjmairair
Copy link
Author

cjmairair commented Oct 18, 2019 via email

@JonathanMagnan
Copy link
Member

Just to let you know, the image didn't appear if you wanted to show something

@cjmairair
Copy link
Author

Here's the image I was trying to upload.

image

@cjmairair
Copy link
Author

I tried the .NET Fiddle and don't see the problem. I'll have to make a little local SQL Server database and test it out.

@JonathanMagnan
Copy link
Member

hello @cjmairair

Did you get the chance to test it out?

Looking forward to hearing from you,

Jon

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@JonathanMagnan JonathanMagnan

Labels
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@Crossbow78 @JonathanMagnan @cjmairair