Class LookupTransformation<TInput, TSource>
The lookup transformation enriches the incoming data with data from the lookup source.
Data from the lookup source is read into memory when the first record arrives.
For each incoming row, the lookup tries to find a matching record in the
loaded source data and uses this record to enrich the ingoing data.
Inheritance
object
LookupTransformation<TInput, TSource>
Inherited Members
object.Equals(object)
object.Equals(object, object)
object.GetHashCode()
object.GetType()
object.MemberwiseClone()
object.ReferenceEquals(object, object)
object.ToString()
Namespace: ETLBox.DataFlow
Assembly: ETLBox.dll
public class LookupTransformation<TInput, TSource> : DataFlowTransformation<TInput, TInput>, ILoggableTask, IDataFlowLogging, IDataFlowTransformation<TInput, TInput>, IDataFlowSource<TInput>, IDataFlowSource, IDataFlowDestination<TInput>, IDataFlowDestination, IDataFlowComponent
Type Parameters
Name | Description |
---|
TInput | Type of ingoing and outgoing data. |
TSource | Type of data used in the lookup source. |
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Constructors
Declaration
public LookupTransformation()
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
public LookupTransformation(IDataFlowExecutableSource<TSource> source, Func<TInput, IEnumerable<TSource>, TInput> retrievalFunc)
Parameters
Type | Name | Description |
---|
IDataFlowExecutableSource<TSource> | source | Sets the Source of the lookup. |
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IEnumerable<T><TSource>, TInput> | retrievalFunc | Sets the RetrievalFunc |
Declaration
public LookupTransformation(IDataFlowExecutableSource<TSource> source)
Parameters
Properties
Declaration
public bool AllowMultipleRows { get; set; }
Property Value
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
The cache mode used for loading data into the lookup cache.
By default, all data is loaded into memory from the lookup source when the first
records arrives at the lookup.
Declaration
public CacheMode CacheMode { get; set; }
Property Value
This function describe how the key is generated for an ingoing row.
If the result of this function match with GetSourceRecordKeyFunc,
both records can be identified as a match.
This function is only need when CacheMode is set to Partial.
Declaration
public Func<TInput, object> GetInputRecordKeyFunc { get; set; }
Property Value
Type | Description |
---|
System.Func<T, TResult><TInput, object> | |
This function describe how the key is generated for a row from the lookup source.
This key is used to store the data in the lookup dictionary.
Declaration
public Func<TSource, object> GetSourceRecordKeyFunc { get; set; }
Property Value
Type | Description |
---|
System.Func<T, TResult><TSource, object> | |
This collection will be used to define the matching columns - will also work with ExpandoObject.
Declaration
public ICollection<MatchColumn> MatchColumns { get; set; }
Property Value
Type | Description |
---|
System.Collections.Generic.ICollection<T><MatchColumn> | |
Only need if you set the CacheMode to PartialDbCache.
This will only work if you use a DbSource as lookup source.
Declaration
public LookupTransformation<TInput, TSource>.PartialDbCacheSettings PartialCacheSettings { get; set; }
Property Value
Declaration
public int ProgressCount { get; }
Property Value
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
The retrieval function that describes how the ingoing data can be enriched with the already pre-read data from
- this one returns not only a list, but a dictionary that provides faster access to the data. The dictionary
key is created with the GetSourceKeyFunc.
the Source.
Declaration
public Func<TInput, IDictionary<object, TSource>, TInput> RetrievalByKeyFunc { get; set; }
Property Value
Type | Description |
---|
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IDictionary<TKey, TValue><object, TSource>, TInput> | |
The retrieval function that describes how the ingoing data can be enriched with the already pre-read data from
the Source.
Declaration
public Func<TInput, IEnumerable<TSource>, TInput> RetrievalFunc { get; set; }
Property Value
Type | Description |
---|
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IEnumerable<T><TSource>, TInput> | |
This collection will be used to define the retrieve columns - will also work with ExpandoObject.
Declaration
public ICollection<RetrieveColumn> RetrieveColumns { get; set; }
Property Value
Type | Description |
---|
System.Collections.Generic.ICollection<T><RetrieveColumn> | |
Declaration
public Func<TInput, IDictionary<object, IList<TSource>>, TInput[]> RetrieveMultipleRowsByKeyFunc { get; set; }
Property Value
Type | Description |
---|
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IDictionary<TKey, TValue><object, System.Collections.Generic.IList<T><TSource>>, TInput[]> | |
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
public Func<TInput, IEnumerable<TSource>, TInput[]> RetrieveMultipleRowsFunc { get; set; }
Property Value
Type | Description |
---|
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IEnumerable<T><TSource>, TInput[]> | |
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
public IDataFlowExecutableSource<TSource> Source { get; set; }
Property Value
SourceBlock from the underlying TPL.Dataflow which is used as output buffer for the component.
Declaration
public override ISourceBlock<TInput> SourceBlock { get; }
Property Value
Type | Description |
---|
ISourceBlock<><TInput> | |
Overrides
ETLBox.DataFlow.DataFlowSource<TInput>.SourceBlock
Holds the data read from the lookup source. This data is used to find data that is missing in the incoming rows.
Declaration
public IEnumerable<TSource> SourceRecords { get; }
Property Value
Type | Description |
---|
System.Collections.Generic.IEnumerable<T><TSource> | |
Holds a dictionary containing data from the lookup spurce.
The dictionary key is generated using the GetSourceRecordKeyFunc
Declaration
public IDictionary<object, TSource> SourceRecordsByKey { get; }
Property Value
Type | Description |
---|
System.Collections.Generic.IDictionary<TKey, TValue><object, TSource> | |
TargetBlock from the underlying TPL.Dataflow which is used as input buffer for the component.
Declaration
public override ITargetBlock<TInput> TargetBlock { get; }
Property Value
Type | Description |
---|
ITargetBlock<><TInput> | |
Overrides
ETLBox.DataFlow.DataFlowTransformation<TInput, TInput>.TargetBlock
A name to identify the task or component. Every component or task comes
with a default name that can be overwritten.
Declaration
public override string TaskName { get; set; }
Property Value
Overrides
If set to true, data types are ignored when comparing data to identify matching records.
E.g. property that stores the integer value 7 would match with a string "7".
Declaration
public bool UseExactNumericTypes { get; set; }
Property Value
Methods
Declaration
protected override void CheckParameter()
Overrides
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
protected override void CleanUpOnFaulted(Exception e)
Parameters
Type | Name | Description |
---|
System.Exception | e | |
Overrides
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
protected override void CleanUpOnSuccess()
Overrides
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
protected override void InitComponent()
Overrides
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
public override IDataFlowSource<ETLBoxError> LinkErrorTo(IDataFlowDestination<ETLBoxError> target)
Parameters
Returns
Overrides
ETLBox.DataFlow.DataFlowSource<TInput>.LinkErrorTo(ETLBox.IDataFlowDestination<ETLBox.ETLBoxError>)
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Declaration
protected override void PrepareParameter()
Overrides
public class Order
{
public int OrderNumber { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class Customer
{
[RetrieveColumn(nameof(Order.CustomerId))]
public int Id { get; set; }
[MatchColumn(nameof(Order.CustomerName))]
public string Name { get; set; }
}
DbSource<Order> orderSource = new DbSource<Order>("OrderData");
CsvSource<Customer> lookupSource = new CsvSource<Customer>("CustomerData.csv");
var lookup = new LookupTransformation<Order, Customer>();
lookup.Source = lookupSource;
DbDestination<Order> dest = new DbDestination<Order>("OrderWithCustomerTable");
source.LinkTo(lookup).LinkTo(dest);
Implements