Hi,

how would I exlude all fields except the FK and Customer.ContactName from this query?

var q = from c in metaData.Customer
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = from o in metaData.Order
                     where o.CustomerId == c.CustomerId
                     select o
        };

This doesn't seem to work:

var q = (from c in metaData.Customer
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = from o in metaData.Order
                     where o.CustomerId == c.CustomerId
                     select o
        }).IncludeFields(p=> p.ContactName);

This neither:

var q = from c in metaData.Customer.IncludeFields(p=> p.ContactName)
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = from o in metaData.Order
                     where o.CustomerId == c.CustomerId
                     select o
        };

Thanks, Patrick

Try this (ref):

var q = (from c in metaData.Customer
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = from o in metaData.Order
                     where o.CustomerId == c.CustomerId
                     select o
        }).IncludeFields(p=> p.ContactName);

daelmo wrote:

Try this

This was one of the samples which doesn't work (see above)..

The type of p is the projection not the entity

Thanks, Patrick

Daelmo used the IncludeFields extension method, while your example used Include.

Walaa wrote:

Daelmo used the IncludeFields extension method, while your example used Include.

Sorry my mistake I meant IncludeFields as there is no Include extension.

pat wrote:

daelmo wrote:

Try this

This was one of the samples which doesn't work (see above)..

The type of p is the projection not the entity

So we are back to the problem now IncludeFields doesn't seem to work with a projection.

Thanks, patrick

Mmm.. Looking deeper at that code, the obvious problem seem to be this (quoted the manual):

Linq to LLBLGen Pro supports the exclusion and inclusion of fields (Adapter, SelfServicing). This is done through the extension methods ExcludeFields and IncludeFields. Using these methods, the developer can specify which fields to exclude in an entity fetch (ExcludeFields) or which fields to use (IncludeFields).

So the thing is that your fetch isn't an entity fetch. So, as _p _is not an entity but an anonymous type, the IncludeFields isn't applicable.

The reason for what David explained is that excluding fields in a custom projection is simply done by changing the custom projection. Excluding/including fields is different as you simply specify an entity fetch (e.g. select c) and you have no control over which fields are fetched, other than: 'all fields' so to control that, include/exclude fields offers you to limit 'all' in a subset of all fields.

Otis wrote:

The reason for what David explained is that excluding fields in a custom projection is simply done by changing the custom projection. Excluding/including fields is different as you simply specify an entity fetch (e.g. select c) and you have no control over which fields are fetched, other than: 'all fields' so to control that, include/exclude fields offers you to limit 'all' in a subset of all fields.

Sorry I don't really understand your answer...

Here my question again:

If my customer has 40 fields and I only need **two **to make my projection (e.g. ContactName and CustomerID) this seems to be the right way to do it but it doesn't work... The SQL statement selects all 40 fields:

var q = from c in metaData.Customer.IncludeFields(p=> p.ContactName)
        where c.Country=="USA"
        select new
        {
            Name = c.ContactName,
            Orders = from o in metaData.Order
                     where o.CustomerId == c.CustomerId
                     select o
        };

Thanks, Patrick

As I wrote the code, I find that very hard to believe (as you're doing a custom projection and you have no full entity element in your projection). Are you absolutely sure you're not confusing the full fields of order with the customer fields?

The query you posted will execute in two queries: the first will have 3 fields: PK field of customer, ContactName and a constant (1). the second query will have all fields of order.

Do you see these two queries? Please use tracing as it might be you're executing more queries and you look at the wrong sql query. (also the includefields in your last query is not doing anything and can be removed).

could you post the two SQL queries executed with the Linq query you posted above?

Your query runs here with:

first query, for fetching customer data. 3 fields in resultset.

Generated Sql query: 
    Query: SELECT [LPLA_1].[ContactName] AS [Name], @LO11 AS [LPFA_2], [LPLA_1].[CustomerID] AS [CustomerId] FROM [Northwind].[dbo].[Customers] [LPLA_1]  WHERE ( ( ( ( ( [LPLA_1].[Country] = @Country2)))))
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @Country2 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "USA".

second query, the order entity data.

Generated Sql query: 
    Query: SELECT [LPLA_2].[OrderID] AS [OrderId], [LPLA_2].[CustomerID] AS [CustomerId], [LPLA_2].[EmployeeID] AS [EmployeeId], [LPLA_2].[OrderDate], [LPLA_2].[RequiredDate], [LPLA_2].[ShippedDate], [LPLA_2].[ShipVia], [LPLA_2].[Freight], [LPLA_2].[ShipName], [LPLA_2].[ShipAddress], [LPLA_2].[ShipCity], [LPLA_2].[ShipRegion], [LPLA_2].[ShipPostalCode], [LPLA_2].[ShipCountry] FROM [Northwind].[dbo].[Orders] [LPLA_2]  WHERE ( ( ( ( [LPLA_2].[CustomerID] IN (@CustomerId1, @CustomerId2, @CustomerId3, @CustomerId4, @CustomerId5, @CustomerId6, @CustomerId7, @CustomerId8, @CustomerId9, @CustomerId10, @CustomerId11, @CustomerId12, @CustomerId13)))))
    Parameter: @CustomerId1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "GREAL".
    Parameter: @CustomerId2 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "HUNGC".
    Parameter: @CustomerId3 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "LAZYK".
    Parameter: @CustomerId4 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "LETSS".
    Parameter: @CustomerId5 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "LONEP".
    Parameter: @CustomerId6 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "OLDWO".
    Parameter: @CustomerId7 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "RATTC".
    Parameter: @CustomerId8 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "SAVEA".
    Parameter: @CustomerId9 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "SPLIR".
    Parameter: @CustomerId10 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "THEBI".
    Parameter: @CustomerId11 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "THECR".
    Parameter: @CustomerId12 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "TRAIH".
    Parameter: @CustomerId13 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "WHITC".

i.o.w. I don't see what you report.

Otis wrote:

i.o.w. I don't see what you report.

Hi Frans,

the query I posted is a simplified query taken from your manual.

I used a simplified one because I wanted to find out if IncludeFields should work in this case....

As you say that it should and it doesn't in my case I'm going to send you my query etc. in the helpdesk area.

Thanks, Patrick

Answered in helpdesk thread.