MSSQL Include (Joins) Seem Case Sensitive

Observing the documentation, https://www.prisma.io/docs/orm/prisma-client/queries/case-sensitivity#microsoft-sql-server-provider and my knowledge of MSSQL I know that MSSQL is case insensitive.

However, it has become apparent that prisma is some how forcing case sensitivity when using an

include

include

on a relationship. We observed this by having some legacy data that was lower case in one spot and a different case in another despite it being a FK. When doing a raw query this works as expected, but through prisma it fails with a null not allowed error message.

model Locations
  ...
  relatedUser String @db.Nvarchar(256)
  ...
  owner: UserInfo @relation('Locations_RelateduserToUserInfo', fields: [relatedUser], references: [Username])
  ...
}

model Locations
  ...
  relatedUser String @db.Nvarchar(256)
  ...
  owner: UserInfo @relation('Locations_RelateduserToUserInfo', fields: [relatedUser], references: [Username])
  ...
}

In our legacy production db we have a value in

UserInfo.Username

UserInfo.Username

as "someuser", but the

Locations.relatedUser

Locations.relatedUser

is "SomeUser". Don't ask me why, I'm not happy with the casing issue within my database to begin with : /

If you jsut run a raw query from SSMS or other query editor, then the following works

SELECT * FROM Locations L
INNER JOIN UserInfo UI ON UI.Username = L.RelatedUser

SELECT * FROM Locations L
INNER JOIN UserInfo UI ON UI.Username = L.RelatedUser

Unfortunately, if you run a query in prisma such as

await prisma.locations.findMany({
  where: {
    id: someLocationId,
  },
  include: {
    owner: true
  }
}

await prisma.locations.findMany({
  where: {
    id: someLocationId,
  },
  include: {
    owner: true
  }
}

then you'll get the following error: "PrismaClientUnknownRequestError"

Inconsistent query result: Field owner is required to return data, got `null` instead.

Inconsistent query result: Field owner is required to return data, got `null` instead.

Please help! We can obviously modify our data in our database, but we have some weird legacy requirements that I'd rather not contend with tat this time. Any help would be greatly appreciated!

Thanks in advance!

Case sensitivity (Reference) | Prisma Documentation

How Prisma Client handles case sensitivity when filtering and sorting.

Prisma•2y ago•

1 reply

jermahs

MSSQL Include (Joins) Seem Case Sensitive

include

include

model Locations
  ...
  relatedUser String @db.Nvarchar(256)
  ...
  owner: UserInfo @relation('Locations_RelateduserToUserInfo', fields: [relatedUser], references: [Username])
  ...
}

model Locations
  ...
  relatedUser String @db.Nvarchar(256)
  ...
  owner: UserInfo @relation('Locations_RelateduserToUserInfo', fields: [relatedUser], references: [Username])
  ...
}

In our legacy production db we have a value in

UserInfo.Username

UserInfo.Username

as "someuser", but the

Locations.relatedUser

Locations.relatedUser

is "SomeUser". Don't ask me why, I'm not happy with the casing issue within my database to begin with : /

If you jsut run a raw query from SSMS or other query editor, then the following works

SELECT * FROM Locations L
INNER JOIN UserInfo UI ON UI.Username = L.RelatedUser

SELECT * FROM Locations L
INNER JOIN UserInfo UI ON UI.Username = L.RelatedUser

Unfortunately, if you run a query in prisma such as

await prisma.locations.findMany({
  where: {
    id: someLocationId,
  },
  include: {
    owner: true
  }
}

await prisma.locations.findMany({
  where: {
    id: someLocationId,
  },
  include: {
    owner: true
  }
}

then you'll get the following error: "PrismaClientUnknownRequestError"

Inconsistent query result: Field owner is required to return data, got `null` instead.

Inconsistent query result: Field owner is required to return data, got `null` instead.

Case sensitivity (Reference) | Prisma Documentation

How Prisma Client handles case sensitivity when filtering and sorting.

MSSQL Include (Joins) Seem Case Sensitive

MSSQL Include (Joins) Seem Case Sensitive

Similar Threads

Similar Threads

Similar Threads