Geek asks and answers

LINQ to SQL rules across method boundaries

To clear my code, I often try to break parts of the data access code in LINQ to SQL into private sub-methods, like using simple business logic code. Let me give you a very simplified example:

public IEnumerable<Item> GetItemsFromRepository()
{
    var setA = from a in this.dataContext.TableA
               where /* criteria */
               select a.Prop;

    return DoSubquery(setA);
}

private IEnumerable<Item> DoSubQuery(IEnumerable<DateTimeOffset> set)
{
     return from item in set
            where /* criteria */
            select item;
}

I’m sure that no imagination will be stretched, presenting more complex examples with a deeper nesting or using the results of sets to filter other queries.

My main question is this: I saw some significant performance differences and even exceptions caused simply by rebuilding LINQ to SQL code in private methods. Can someone explain the rules for this behavior so that I can make informed decisions on how to write an effective clean data access code?

Some questions that I had:

1) System.Linq.Table ?

2) System.Linq.Table ?

3) , (Take, First, Last, order by ..) System.Linq.Table ?

+5
2

:

public IQueryable<Item> GetItemsFromRepository()
{
    var setA = from a in this.dataContext.TableA
               where /* criteria */
               select a.Prop;

    return DoSubquery(setA);
}

private IQueryable<Item> DoSubQuery(IQueryable<DateTimeOffset> set)
{
     return from item in set
            where /* criteria */
            select item;
}

IQueryable<Item> IEnumerable<Item> AsEnumerable() IQueryable<Item>. , , , IQueryable , , GetItemsFromRepository(), .

:

1) System.Linq.Table ?

- , Max(), ToList() .., , , -it-go .

, AsEnumerable() , , , , AsEnumerable() , , , .

2) System.Linq.Table ?

, . Table<T> IQueryable<T>. , , , , - .

3) (Take, First, Last, order by ..) System.Linq.Table ?

, IQueryable<T>.

: IEnumerable IQueryable.

, IQueryable, IEnumerable , , .

IQueryable linq linqy, Take(), Select(), GroupBy ..

, . , System.Linq.Data.Table SQL-, . , mySource - , :

var filtered = from item in mySource
  where item.ID < 23
  select new{item.ID, item.Name};

foreach(var i in filtered)
  Console.WriteLine(i.Name);

SQL :

select id, name from mySourceTable where id < 23

, MoveNext() .

, mySource a List HashSet - , IEnumerable<T>, , linq-to-objects :

foreach(var item in mySource)
  if(item.ID < 23)
    yield return new {item.ID, item.Name};

, . , :

, IQueryable<T> IEnumerable<T>, , , mySource ( ) :

var filtered = from item in mySource.AsEnumerable()
  where item.ID < 23
  select new{item.ID, item.Name};

, , , , , :

var asEnum = mySource.AsEnumerable();
var filtered = from item in asEnum
  where item.ID < 23
  select new{item.ID, item.Name};

, SQL SELECT * FROM mySourceTable, linq-to-objects .

, , 10 < 23 50 000 , , .

, AsEnumerable(), IQueryable<T> IEnumerable<T>. foreach , IEnumerable<T>, , , , , DoSubQuery , IEnumerable<DateTimeOffset> a IEnumerable<Item>; AsEnumerable() IQueryable<DateTimeOffset> IQueryable<Item> , , .

99% IQueryable .

, , , AsEnumerable() IEnumerable<T> , . -, IEnumerable<T> , , , , (, , XML ..),

, IEnumerable<T> . :

IQueryable<IGrouping<string, int>> groupingQuery = from item in mySource select item.ID group by item.Name;
var list1 = groupingQuery.Select(grp => new {Name=grp.Key, Count=grp.Count()}).ToList();//fine
foreach(var grp in groupingQuery)//disaster!
  Console.WriteLine(grp.Count());

groupingQuery , , . list1, IQueryable , , SQL, - :

select name, count(id) from mySourceTable group by name

. , .

, SQL group by, , , :

select distinct name from mySourceTable,

, , :

select id from mySourceTable where name = '{name found in last query goes here}'

, 2 SQL-, 200 000.

mySource.AsEnumerable(), . ( mySource.Select(item => new {item.ID, item.Name}).AsEnumerable(), , , ).

, , IQueryable<T> . , , .

+3

LINQ-to-SQL : IEnumerable<T>, , . :

  • IQueryable<T>, , , where , TSQL, , ,
  • List<T> , , , , LINQ-to-Objects

- : LINQ-to-Objects ( IEnumerable<T>), - ( , , )

+5

More articles:


All Articles