fks | 前端技能汇总 Frontend Knowledge Structure | State Container library

Using EF Core 5.0, I have a PK-less entity (from a SQL view) OrderInfo, which has a column OrderDetailId. I also have an entity DiscountOrder which a PK from the columns OrderDetailId and DiscountId.

I would like to create a navigation property from Order to DiscountOrders. Such as:

...

I'm trying to generate some data for testing by duplicating existing data in my database. There are a number of tables linked by FKs and I want to keep the same data profile. I think it's easiest to explain with an example:

CustomerID Name Age 1 Fred 20 2 Bob 30 3 Joe 40 InvoiceID CustomerID Date 1 1 2020-01-01 2 2 2020-02-02 3 2 2020-03-03 4 3 2020-04-04 LineItemID InvoiceID Item Price Qty 1 1 Apples 1.5 5 2 2 Oranges 2 3 3 2 Peaches 2.5 6 4 3 Grapes 3 10 5 4 Pineapple 5 1

I want to duplicate all the customers who are older than 18, including all of their linked data. So for the Customers table it's easy enough to do something like:

...

I am trying to configure a relationship between to entities in EFCore: Square and Position. Position has one (position) to many (squares) relationship with square named squares, as well as two one - to - one relationship between Square named enPassentTakablePawnOfWhite and enPassentTakablePawnOfBlack. Note that both the FKs as well as the PKs in both of the entities are shadow properties:

...

As I found out to my surprise MySQL allows FKs to non-unique columns. I am not sure if this applies to other databases as well and always thought the FK had to be unique - otherwise, how do we know the parent row of a child - but looks like that is not the case. Here is a fiddle that illustrates this. We first create 3 tables:

...

I want to turn off ALL (or at least most of) conventions in Entity Framework Core (and I am talking about EF Core 5 or above) and then build the whole model "by hands".

One may wonder why.

Here is why: I have a task to migrate several large legacy databases from Entity Framework 6 (EF) to Entity Framework Core 5 (EFC). This involves many hundreds of tables and several databases. Some of these databases are created using a Code First approach and some are just third party databases, which we need to query and update from C# code. For the latter databases we must match their schema exactly.

Due to the size of the problem both EF and EFC flavors of the code must coexist for, let's say, several months. This can be easily achieved by using conditional compilation (see below).

Most likely anything that is not supported or is inconveniently supported in EFC in comparison to EF (or was "hacked" into EF models), like spatial indexes, multi-column KeyAttribute PKs, multi-column ForeignKeyAttribute FKs, self-referencing multiple times tables, multiple indexes defined on the same columns (some are filters and some are just regular indexes), and so on and so forth is there.

That's fine. I can easily deal with EFC inability to deal with that by "overriding" the attributes using conditional compilation, e.g.

...

Situation:

• Code First C# Azure Function for Posting a new Activity
• Activity has Id, Helper and Round. Last two are also entities, ie FKs
• Database is Azure SQL
• Whilst I can Post new Helpers and Rounds, when posting a new Activity using existing Helper and Round... I'm Getting this error:

...

I have the following models:

...

How do I get the first X distinct combinations of an Oracle SQL CROSS JOIN such that: if a row with values (a, b) exists, there are no other columns within X that repeat a or b?

In my example, I have two tables with 1000 * 1000 = 1,000,000 combinations - but I only want the first 500 unique pairs. These seem to occur at ROWNUM = 1, 1001, 2001, 3001, etc.

I have a Customers and Books table - and I want to generate 500 random book sales [a table which references both customers/books as FKs] such that there are 500 unique customers, and 500 unique books. I have done a cross join of Customers to Books, but this gives a cartesian product of all book/customer combinations. How do I get 500 unique customers, and 500 unique books?

Thanks.

...

I have very interesting scenario and I really am looking for any idea on how to proceed. I have schema with around 60 tables. I want find a way to order the tables based on their FKs connections to determine their creation order. What I mean is

I'm providing very simple example of what I try to achieve.

We have table USERS with columns id,role_id, tableROLES with columns id and name and table TASKS with columns id, user_id and name.

USERS.user_id is a FK to ROLES.id and TASKS.user_id is a FK to USERS.id

So when creating the schema the creation order of tables should be 1st ROLES, 2nd USERS and 3rd TASKS, to be able to add FK constraints by the time of creation without table altering.

Is there a tool or maybe some idea of SQL query which can give me that picture, because for 3 tables it's relatively easy, but for 60 tables with a lot of FK connections this will be a nightmare. Thanks in advance. The database I am using is SQL server.

...