asp.netsql-serverdbml

in DBML, how to get child table with specific fields rather than all fields


We are using SQL Server and application is developed with DBML. Now due to the functionality of eager loading I am having an issue. I have a table A which have relationship with table B. (A -> B). Now when I try to load table A, it will get all fields of table B. Culprit is such that table B is having 2-3 columns which is very heavy, contains byte array data and due to those column it takes too much load for getting data of table A also.

Question
Can I have such way that I can load only few columns (not all columns) of table B when I am getting table A?

What I have tried
I am getting this error :

The expression specified must be of the form p.A, where p is the parameter and A is a property or field member.

when I tried with below code -

DataContext2.DeferredLoadingEnabled = false;
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<A>(x => x.Campaign);
options.LoadWith<A>(x => x.AnotherTable);
options.LoadWith<A>(x => x.B.Select(o => new B
{
    Id = o.Id,
    Name = o.Name,
    Person = o.Person,
}).ToList());
DataContext2.LoadOptions = options;

Solution

  • Just use join and select only necessary columns:

    var yourQuery = (from t_A in dbContext.Table_A
                 join t_B in dbContext.Table_B on t_A.ID equals t_B.ID                 
                 //use where operator to filter rows
                 select new {
                     Id = t_B.Id,
                     Name = t_B.Name,
                    Person = t_B.Person
                      // any field you want from your query
                 }).ToList();