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
LoggableTask
DataFlowComponent
DataFlowSource<TInput>
DataFlowTransformation<TInput, TInput>
LookupTransformation<TInput, TSource>
LookupTransformation
Implements
IDataFlowTransformation<TInput, TInput>
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
Syntax
    public class LookupTransformation<TInput, TSource> : DataFlowTransformation<TInput, TInput>, ILoggableTask, IDataFlowLogging, IDataFlowTransformation<TInput, TInput>, IDataFlowSource<TInput>, IDataFlowSource, IDataFlowDestination<TInput>, IDataFlowDestination, IDataFlowComponent
Type Parameters
NameDescription
TInput

Type of ingoing and outgoing data.

TSource

Type of data used in the lookup source.

Examples
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

LookupTransformation()

Declaration
    public LookupTransformation()
Examples
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);

LookupTransformation(IDataFlowExecutableSource<TSource>, Func<TInput, IEnumerable<TSource>, TInput>)

Declaration
    public LookupTransformation(IDataFlowExecutableSource<TSource> source, Func<TInput, IEnumerable<TSource>, TInput> retrievalFunc)
Parameters
TypeNameDescription
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

LookupTransformation(IDataFlowExecutableSource<TSource>)

Declaration
    public LookupTransformation(IDataFlowExecutableSource<TSource> source)
Parameters
TypeNameDescription
IDataFlowExecutableSource<TSource>source

Sets the Source of the lookup.

Properties

AllowMultipleRows

Declaration
    public bool AllowMultipleRows { get; set; }
Property Value
TypeDescription
bool
Examples
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);

CacheMode

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
TypeDescription
CacheMode

GetInputRecordKeyFunc

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
TypeDescription
System.Func<T, TResult><TInput, object>

GetSourceRecordKeyFunc

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
TypeDescription
System.Func<T, TResult><TSource, object>

MatchColumns

This collection will be used to define the matching columns - will also work with ExpandoObject.

Declaration
    public ICollection<MatchColumn> MatchColumns { get; set; }
Property Value
TypeDescription
System.Collections.Generic.ICollection<T><MatchColumn>

PartialCacheSettings

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
TypeDescription
LookupTransformation<TInput, TSource>.PartialDbCacheSettings

ProgressCount

Declaration
    public int ProgressCount { get; }
Property Value
TypeDescription
int
Examples
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);

RetrievalByKeyFunc

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
TypeDescription
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IDictionary<TKey, TValue><object, TSource>, TInput>

RetrievalFunc

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
TypeDescription
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IEnumerable<T><TSource>, TInput>

RetrieveColumns

This collection will be used to define the retrieve columns - will also work with ExpandoObject.

Declaration
    public ICollection<RetrieveColumn> RetrieveColumns { get; set; }
Property Value
TypeDescription
System.Collections.Generic.ICollection<T><RetrieveColumn>

RetrieveMultipleRowsByKeyFunc

Declaration
    public Func<TInput, IDictionary<object, IList<TSource>>, TInput[]> RetrieveMultipleRowsByKeyFunc { get; set; }
Property Value
TypeDescription
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IDictionary<TKey, TValue><object, System.Collections.Generic.IList<T><TSource>>, TInput[]>
Examples
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);

RetrieveMultipleRowsFunc

Declaration
    public Func<TInput, IEnumerable<TSource>, TInput[]> RetrieveMultipleRowsFunc { get; set; }
Property Value
TypeDescription
System.Func<T1, T2, TResult><TInput, System.Collections.Generic.IEnumerable<T><TSource>, TInput[]>
Examples
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);

Source

The source component from which the lookup data is retrieved. E.g. a DbSource or a MemorySource.

Declaration
    public IDataFlowExecutableSource<TSource> Source { get; set; }
Property Value
TypeDescription
IDataFlowExecutableSource<TSource>

SourceBlock

SourceBlock from the underlying TPL.Dataflow which is used as output buffer for the component.

Declaration
    public override ISourceBlock<TInput> SourceBlock { get; }
Property Value
TypeDescription
ISourceBlock<><TInput>
Overrides
ETLBox.DataFlow.DataFlowSource<TInput>.SourceBlock

SourceRecords

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
TypeDescription
System.Collections.Generic.IEnumerable<T><TSource>

SourceRecordsByKey

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
TypeDescription
System.Collections.Generic.IDictionary<TKey, TValue><object, TSource>

TargetBlock

TargetBlock from the underlying TPL.Dataflow which is used as input buffer for the component.

Declaration
    public override ITargetBlock<TInput> TargetBlock { get; }
Property Value
TypeDescription
ITargetBlock<><TInput>
Overrides
ETLBox.DataFlow.DataFlowTransformation<TInput, TInput>.TargetBlock

TaskName

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
TypeDescription
string
Overrides

UseExactNumericTypes

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
TypeDescription
bool

Methods

CheckParameter()

Declaration
    protected override void CheckParameter()
Overrides
Examples
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);

CleanUpOnFaulted(Exception)

Declaration
    protected override void CleanUpOnFaulted(Exception e)
Parameters
TypeNameDescription
System.Exceptione
Overrides
Examples
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);

CleanUpOnSuccess()

Declaration
    protected override void CleanUpOnSuccess()
Overrides
Examples
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);

InitComponent()

Declaration
    protected override void InitComponent()
Overrides
Examples
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);

LinkErrorTo(IDataFlowDestination<ETLBoxError>)

Declaration
    public override IDataFlowSource<ETLBoxError> LinkErrorTo(IDataFlowDestination<ETLBoxError> target)
Parameters
TypeNameDescription
IDataFlowDestination<ETLBoxError>target
Returns
TypeDescription
IDataFlowSource<ETLBoxError>
Overrides
ETLBox.DataFlow.DataFlowSource<TInput>.LinkErrorTo(ETLBox.IDataFlowDestination<ETLBox.ETLBoxError>)
Examples
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);

PrepareParameter()

Declaration
    protected override void PrepareParameter()
Overrides
Examples
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