---
title: "How do I use Luminesce to read/write custom entities?"
slug: "how-do-i-use-luminesce-to-readwrite-custom-entities"
updated: 2024-11-28T09:19:16Z
published: 2024-11-28T09:19:16Z
canonical: "support.lusid.com/how-do-i-use-luminesce-to-readwrite-custom-entities"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# How do I use Luminesce to read/write custom entities?

By default, Luminesce knows nothing about your [custom entities](/v1/docs/extending-lusids-data-model-using-custom-entities), so providers for them do not exist out-of-the-box. You must create a read/write provider pair for each type of custom entity you wish to interact with.

Once created, you can use your read/write provider pair to:

- Create custom entities of the type if they do not exist, and update them if they do.
- Retrieve custom entities you have created.
- Delete custom entities.

In this guide we'll see how to create a read/write provider pair to interact with example 'Office' custom entities, representing corporate office locations.

## Defining an Office custom entity type

The first step is to create a *custom entity type* defining core characteristics for Office custom entities. [Find out more about custom entity types](/v1/docs/extending-lusids-data-model-using-custom-entities#defining-a-custom-entity-type).

|  | **Mandatory fields** | **Example values** |
| --- | --- | --- |
| Identifier | `EntityTypeName` | `Office` |
| Built-in data fields | `DisplayName` | `Office location` |
| `Description` | `An office or branch location` |
| User-specified data fields | `FieldName` | `address` | `isHeadOffice` | `seatingCapacity` |
| `FieldLifeTime` | `Perpetual` | `TimeVariant` | `TimeVariant` |
| `FieldType` | `String` | `Boolean` | `Decimal` |
| `FieldCollectionType` | `Single` | `Single` | `Single` |
| `FieldRequired` | `True` | `True` | `False` |

Note the following:

- LUSID automatically applies a `~` character to the `EntityTypeName` to create an `EntityType` identifier (in this case) of `~Office`. The `~` is to distinguish custom entity types from LUSID's [built-in entity types](/v1/docs/what-is-an-entity).
- All custom entity types have two mandatory built-in data fields: `DisplayName` and `Description`.
- Our Office custom entity type has three user-specified data fields: `address`, `isHeadOffice `and `seatingCapacity`, the first two of which are required.
- `isHeadOffice` and `seatingCapacity` are [time-variant](/v1/docs/what-is-bitemporal-data#understanding-perpetual-and-timevariant-data-items) data fields (that is, values are expected to vary during different time periods), whereas `address` is perpetual.

You can use the [Lusid.CustomEntity.Definition.Writer](/v1/docs/lusidcustomentitydefinitionwriter) provider to create a custom entity type, for example:

```sql
@data_fields = values
('address', 'Perpetual', 'String', 'Single', True, 'The postal address of the location'),
('isHeadOffice', 'TimeVariant', 'Boolean', 'Single', True, 'Whether or not the location is a head office')
('seatingCapacity', 'TimeVariant', 'Decimal', 'Single', False, 'The seating capacity of the location');

@table_of_data = select 'Office' as EntityTypeName, 'Office location' as DisplayName,
'An office or branch location' as Description, column1 as FieldName,
column2 as FieldLifeTime, column3 as FieldType, column4 as FieldCollectionType,
column5 as FieldRequired, column6 as FieldDescription from @data_fields;

select * from Lusid.CustomEntity.Definition.Writer where toWrite = @table_of_data;
```

## Creating identifiers for Office custom entities as property types

Each custom entity must have at least one *identifier*.

Consider the following JSON fragment representing 'One Carter Lane', a corporate location that is both FINBOURNE Technology's London office and European headquarters, and so has an identifier for each context:

```json
"displayName": "One Carter Lane",
"description": "FINBOURNE office and EMEA regional headquarters",
"identifiers": [
  {
    "identifierScope": "Location",
    "identifierType": "OfficeId",
    "identifierValue": "London"
  },
  {
    "identifierScope": "Location",
    "identifierType": "HeadquartersId",
    "identifierValue": "Europe"
  }
],
...
```

An identifier consists of three components: an `identifierScope`, `identifierType` and `identifierValue`. The values you assign to these components combine to uniquely identify One Carter Lane in each of the contexts in which it operates. In this example:

| **To uniquely identify One Carter Lane as a ...** | **identifierScope** | **identifierType** | **IdentifierValue** |
| --- | --- | --- | --- |
| Office location | `Location` | `OfficeId` | `London` |
| Headquarters | `Location` | `HeadquartersId` | `Europe` |

For each identifier you intend to give a custom entity, you must first use the [Lusid.Property.Definition.Writer](/v1/docs/lusidpropertydefinitionwriter) provider to create an underlying [property type](/v1/docs/how-do-i-create-a-property-type) with the following characteristics:

- A `Domain` of `CustomEntity`.
- A `PropertyScope` with the `identifierScope` value, for example `Location`.
- A `PropertyCode` with the `identifierType` value, for example `OfficeId` or `HeadquartersId`.
- A `ConstraintStyle` of `Identifier`.
- A `LifeTime` of `Perpetual`.

### Creating a property type to constitute an 'Office location' identifier

The following query creates a property type with a 3-stage property key of `CustomEntity/Location/OfficeId`:

```sql
@table_of_data = select 'CustomEntity' as Domain, 'Location' as PropertyScope, 'OfficeId' as PropertyCode, 'Identifier' as ConstraintStyle,
'Perpetual' as LifeTime, 'system' as DataTypeScope, 'string' as DataTypeCode, 'This is an Office ID' as DisplayName, 'insert' as WriteAction;

select * from Lusid.Property.Definition.Writer where ToWrite = @table_of_data;
```

### Creating a property type to constitute a 'Headquarters' identifier

The following query creates a property type with a 3-stage property key of `CustomEntity/Location/HeadquartersId`:

```sql
@table_of_data = select 'CustomEntity' as Domain, 'Location' as PropertyScope, 'HeadquartersId' as PropertyCode, 'Identifier' as ConstraintStyle,
'Perpetual' as LifeTime, 'system' as DataTypeScope, 'string' as DataTypeCode, 'This is a Headquarters ID' as DisplayName, 'insert' as WriteAction;

select * from Lusid.Property.Definition.Writer where ToWrite = @table_of_data;
```

## Creating an Office read/write provider pair in the Luminesce catalog

With the custom entity type and identifier property types in place, you can create:

- A `Lusid.CustomEntity.Office` provider to retrieve Office custom entities from LUSID.
- A `Lusid.CustomEntity.Office.Writer` provider to create, update or delete Office custom entities.

These providers are added to the Luminesce catalog for [suitably-permissioned users](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users) to use:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/41014dc0-87b7-428e-99f7-0abe5b267cb7.png)

You create the read and write providers in the same operation by writing a special Luminesce SQL query using the [Sys.Admin.Lusid.Provider.Configure](/v1/docs/how-do-i-configure-luminesce-entity-providers-to-readwrite-properties#using-the-sysadminlusidproviderconfigure-provider) provider. You must supply the property keys of the identifiers and the name of the custom entity type; `Sys.Admin.Lusid.Provider.Configure` automatically extracts information about the built-in and user-specified data fields from the custom entity type. For example:

```sql
@identifiersToCatalog = values
('CustomEntity/Location/HeadquartersId', 'HeadquartersId', True, 'An identifier for a custom entity as a headquarters'),
('CustomEntity/Location/OfficeId', 'OfficeId', True, 'An identifier for a custom entity as an office');

@config = select column1 as [Key], column2 as Name, column3 as IsMain, column4 as Description from @identifiersToCatalog;

select * from Sys.Admin.Lusid.Provider.Configure
where Provider = 'Lusid.CustomEntity.Office'
and Configuration = @config;
```

Click the **Refresh** button in the Luminesce catalog (yellow box below) to see the populated `Lusid.CustomEntity.Office` and `Lusid.CustomEntity.Office.Writer` providers:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/a8db0fca-67bf-4e60-ba05-b4888b821f1c.png)

Note that:

- Blue columns are built-in data fields extracted from the custom entity type.
- Green columns are user-specified data fields extracted from the custom entity type.
- Red columns are identifiers extracted from property types.

## Creating Office custom entities using Lusid.CustomEntity.Office.Writer

You can now use `Lusid.CustomEntity.Office.Writer` to create as many Office custom entities (that is, instances of the `~Office` custom entity type) as you need.

For example, the following query creates three Office custom entities for FINBOURNE, one for each of the Singapore, London and New York offices. Note the following:

- You must specify a value for at least one identifier (`OfficeId` and/or `HeadquartersId`)
- You must specify a value for each built-in data field (`DisplayName` and `Description`)
- You must specify a value for each user-specified data field described as required in the custom entity type (`isHeadOffice` and `address`)
- `'Upsert' as WriteAction` sets the operation to 'insert or update' (as opposed to delete):

```sql
@vals = values
('Singapore', 'Asia', '8 Marina Boulevard', '8 Marina Boulevard, 018981, Singapore', 10, 'FINBOURNE office and APAC regional headquarters'),
('London', 'Europe', 'One Carter Lane', 'One Carter Lane, London, England, EC4V 5ER', 150, 'FINBOURNE office and EMEA regional headquarters'),
('NewYork', 'USA', '25 W 39th St', '25 W 39th St, 7th Floor, New York, NY, 10018', 25, 'FINBOURNE office and Americas regional headquarters');

@table_of_data = select column1 as OfficeId, column2 as HeadquartersId, True as isHeadOffice, column3 as DisplayName, 
column6 as Description, column5 as seatingCapacity, column4 as address, 'Upsert' as WriteAction from @vals;

select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;
```

### Updating existing custom entities

To update an existing custom entity, you must specify:

- At least one identifier
- The `DisplayName` and `Description` built-in data fields
- All required user-specified data fields (`isHeadOffice` and `address`)

...whether you are updating their actual values or not.

For example, to update Singapore's `DisplayName` and `address`:

```sql
@table_of_data = select
  'Singapore' as OfficeId,
  '7 Marina Boulevard' as DisplayName,
  'FINBOURNE office and APAC regional headquarters' as Description,
  True as isHeadOffice,
  '7 Marina Boulevard, 018981, Singapore' as address,
  'Upsert' as WriteAction;
select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;
```

### Specifying values for time-variant user-specified data fields

A time-variant data field can have different values during different time periods. For example, Singapore's `seatingCapacity` might be `10` during the 2022 calendar year, but `15` during 2023.

You can use the `EffectiveFrom` column to specify a datetime from which a time-variant data field is valid. Note if you omit `EffectiveFrom`, time-variant data fields are valid from 1 January 0001 to 31 December 9999 (that is, effectively all of time), the same as perpetual data fields, identifiers, and the `DisplayName` and `Description` built-in data fields.

Since in our example another time-variant data field, `isHeadOffice`, is required, we must union two records in order to set a value of `15` for `seatingCapacity` effective 1 January 2023:

- The first record sets `seatingCapacity` and `EffectiveFrom` but specifies `null` for `isHeadOffice` to ignore it.
- The second record sets `isHeadOffice` to `True` (its original value) and specifies `null` for `seatingCapacity` and `EffectiveFrom` to ignore them (essentially leaving `isHeadOffice` as-is).

The end result is that `seatingCapacity` is set to `10` between 1 January 0001 and 31 December 2022, and `15` between 1 January 2023 and 31 December 9999. Without the union, the required, time-variant `isHeadOffice` data field would also be divided into these two time periods:

```sql
@table_of_data = select
 'Singapore' as OfficeId,
 '7 Marina Boulevard' as DisplayName,
 'FINBOURNE office and APAC regional headquarters' as Description,
 null as isHeadOffice,
 15 as seatingCapacity,
 #2023-01-01# as EffectiveFrom
union all
select
 'Singapore' as OfficeId,
 '7 Marina Boulevard' as DisplayName,
 'FINBOURNE office and APAC regional headquarters' as Description,
 True as isHeadOffice,
 null as seatingCapacity,
 null as EffectiveFrom
;

select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;
```

### Deleting custom entities

You can delete one or more custom entities by specifying a single identifier for each and using `'Delete' as WriteAction`:

```sql
@vals = values
('Singapore'),
('NewYork');

@table_of_data = select column1 as OfficeId, 'Delete' as WriteAction from @vals;

select * from Lusid.CustomEntity.Office.Writer where ToWrite = @table_of_data;
```

## Retrieving Office custom entities using Lusid.CustomEntity.Office

You can use `Lusid.CustomEntity.Office` to retrieve custom entities in the usual way for a read provider, specifying a filter on any column. For example:

```sql
select * from Lusid.CustomEntity.Office where OfficeId = 'London';
```

Note that filtering is currently faster on `DisplayName` than on any identifier. So for example the query:

```sql
select * from Lusid.CustomEntity.Office where DisplayName = 'One Carter Lane';
```

...is faster than the query above.

To examine different values for time-variant data fields, use the `EffectiveAt` parameter. For example, to retrieve values for Singapore's `seatingCapacity`:

```sql
-- Specifying an effective at date during the 2022 calendar year returns 10
select * from Lusid.CustomEntity.Office where OfficeId = 'Singapore' and EffectiveAt = #2022-06-01#;
-- Specifying an effective at date during the 2023 calendar year returns 15
select * from Lusid.CustomEntity.Office where OfficeId = 'Singapore' and EffectiveAt = #2023-06-01#;
```

## Administering the Office read/write provider pair

### Changing the providers

You can retrieve the definition used to create a read/write provider pair using the `Sys.File` provider and supplying the name of the CSV file used to store it. This takes the form `<custom-entity-type>customentityproviderfactory.csv`, for example `Officecustomentityproviderfactory.csv`:

```sql
select Content from Sys.File where Name = 'Officecustomentityproviderfactory' and Extension = '.csv';
```

You might want to examine the CSV file in preparation for making a change. You can re-run the `Sys.Admin.Lusid.Provider.Configure` query at any time to specify a different definition, and then click the **Refresh** button in the Luminesce catalog to reload the providers.

### Deleting the providers

You can remove a read/write provider pair from the Luminesce catalog by re-running the `Sys.Admin.Lusid.Provider.Configure` query, this time with `WriteAction = 'Reset'`, for example:

```sql
select * from Sys.Admin.Lusid.Provider.Configure
where Provider = 'Lusid.CustomEntity.Office'
and WriteAction = 'Reset';
```