CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-sqllinq-to-entities
PessimisticPaperClip
PessimisticPaperClip

Reputation: 35

Is it possible to reuse parts of a LINQ to Entities select statement?

When using LINQ to Entities, is there a way to reuse a block of a select statment in other select stements?
For example, in the below code, I'm using LINQ to select a customer device object from my database. I also select a related Model object from a table that is joined to the customer devices table. 

            list = context.PTT_CUSTOMER_DEVICES
                .Include(...)
                .Select(d => new CustomerDevice
                {
                    customerAssetTag = d.CustomerAssetTag,
                    customerDeviceID = d.CustomerDeviceID,
                    //This section is used in several LINQ statements throughout the application.
                    Model = new Model()
                    {
                        ModelID = d.PTS_MODELS.ModelID,
                        Name = d.PTS_MODELS.Name,
                        Make = new Make()
                        {
                            MakeID = d.PTS_MODELS.PTS_MAKES.MakeID,
                            Name = d.PTS_MODELS.PTS_MAKES.Name
                        }
                    }
                 })...

There are, however, other objects in the database that also reference the Model table. In my select statements for those other tables, I basically copied the same Model = new Model() code to a select statement for those different tables.
What I'm wondering is, is it possible to store and reuse that block of code in multiple selects? Possibly using an extension method?

Upvotes: 1

Views: 1312

Answers (4)

Kleberson Leite
Kleberson Leite

Reputation: 29

You can use Expression:

public static Expression<Func<PTT_CUSTOMER_DEVICES, CustomerDevice>>
            CustomerDeviceExpression = d =>
                new CustomerDevice
                {
                    customerAssetTag = d.CustomerAssetTag,
                    customerDeviceID = d.CustomerDeviceID,

                    Model = new Model()
                    {
                        ModelID = d.PTS_MODELS.ModelID,
                        Name = d.PTS_MODELS.Name,
                        Make = new Make()
                        {
                            MakeID = d.PTS_MODELS.PTS_MAKES.MakeID,
                            Name = d.PTS_MODELS.PTS_MAKES.Name
                        }
                    }
                 };

Then:

list = context.PTT_CUSTOMER_DEVICES
            .Include(...)
            .Select(CustomerDeviceExpression)...

Upvotes: 0

NetMage
NetMage

Reputation: 26917

You would need to create a constructor for Model that takes a CustomerDevice? (what ever the entity is for PTT_CUSTOMER_DEVICES:

public Model(CustomerDevice d) {
    ModelID = d.PTS_MODELS.ModelID;
    Name = d.PTS_MODELS.Name;
    Make = new Make() {
        MakeID = d.PTS_MODELS.PTS_MAKES.MakeID,
        Name = d.PTS_MODELS.PTS_MAKES.Name
    };
}

Then you could just call that constructor in the LINQ:

Model = new Model(d),

Upvotes: 0

Kerry
Kerry

Reputation: 36

As an alternative to Func<>s, you can also make use of extension methods to reuse the conversions between your entity types to and other POCOs.

public static IQueryable<CustomerDevice> ToCustomerDevice(this IQueryable<PTT_CUSTOMER_DEVICES> devices)
{
    return devices.Select(d => new CustomerDevice
    {
        customerAssetTag = d.CustomerAssetTag,
        customerDeviceID = d.CustomerDeviceID
    }
}

However, EF will not allow you to nest these, and will complain that it cannot convert the nested extension method to SQL.

A work around for this can be to perform the transform in memory, rather than in SQL:

public static Model ToModel(this PTS_MODELS model)
{
    return new Model()
    {
        ModelID = model.ModelID,
        Name = model.Name,
        Make = new Make()
        {
            MakeID = model.PTS_MAKES.MakeID,
            Name = model.PTS_MAKES.Name
        }
    };
}

public static IEnumerable<CustomerDevice> ToCustomerDevice(this IQueryable<PTT_CUSTOMER_DEVICES> devices)
{
    return devices
        .Include(d => d.PTS_MODELS.PTS_MAKES)
        .AsEnumerable() // Evaulate everything that follows in memory
        .Select(d => new CustomerDevice
        {
            customerAssetTag = d.CustomerAssetTag,
            customerDeviceID = d.CustomerDeviceID,
            Model = d.PTS_MODELS.ToModel()
        });
}

Since you are now returning an IEnumerable<> any further Where() conditions will be evaluated in memory, rather than in SQL, so its important that ToCustomerDevice() is your last call.

Upvotes: 1

Enigmativity
Enigmativity

Reputation: 117084

If those tables have a common base class or implement the same interface you can do it.

To make it simple assume I have a IQueryable<string> names. I can sort it like this:

IQueryable<string> sorted = names.OrderBy(name => name);

But if I want to keep this particular sort to use later I can do this:

Func<IQueryable<string>, IQueryable<string>> orderBy = q => q.OrderBy(x => x);

Now to call it I just pass any IQueryable<string> in:

IQueryable<string> sorted = orderBy(names);

Upvotes: 0

Related Questions