Xem ti vi online

Design by Blogcrowds

Thursday, October 8, 2015

C#: Left outer joins with LINQ

0 comments Posted by Duc Nguyen at 5:15 AM

Consider this very simple query (Assuming a scenario that not all the TimesheetLines are associated with a Job)
1
2
3
Select TL.EntryDate, TL.Hours, J.JobName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo
A LINQ query using inner join is
1
2
3
4
5
6
7
8
9
10
11
var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo
    where tl.ResourceNo == resourceNo
 
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };
And a LINQ query performing left join is
1
2
3
4
5
6
7
8
9
10
11
12
var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo into tl_j
    where tl.ResourceNo == resourceNo
 
    from j in tl_j.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };
Notice that the only difference is the use of “into” with the join statement followed by reselecting the result using “DefaultIfEmpty()” expression. And here’s the generated SQL for the above LINQ expression.
1
2
3
4
SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job]
FROM [dbo].[TimeSheetLine] AS [t0]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
WHERE [t0].[ResourceNo] = @p0
Another LINQ version which is more compact is:
1
2
3
4
5
6
7
8
9
var lines =
    from tl in db.TimeSheetLines
    from j in db.Jobs.Where(j=>j.JobNo == tl.JobNo).DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };
Similarly, this concept can be expanded for multiple left joins. Assuming that a TimeSheetLine will either have a JobNo or an IndirectCode, consider this SQL query:
1
2
3
4
Select TL.EntryDate, TL.Hours, J.JobName, I.IndirectName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo
Left Join Indirects I on TL.IndirectCode=I.IndirectCode
The equivalent LINQ query is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var lines =
    from tl in db.TimeSheetLines
    join j in db.Jobs      on tl.JobNo        equals j.JobNo         into tl_j
    join i in db.Indirects on tl.IndirectCode equals i.IndirectCode  into tl_i
    where tl.ResourceNo == resourceNo
 
    from j in tl_j.DefaultIfEmpty()
    from i in tl_i.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName,
        Indirect = i.IndirectName,
    };
And the generated SQL is:
1
2
3
4
SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job], [t2].[IndirectName] As [Indirect]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
LEFT OUTER JOIN [dbo].[Indirects] AS [t2] ON [t0].[IndirectCode] = [t2].[IndirectCode]
WHERE [t0].[ResourceNo] = @p0
That’s all, left outer joins in LINQ are as easy as in T-SQL. Happy joining.
Update:
Notice that this post describes the approach to perform a Left Outer Join in LINQ To SQL as well as Entity Framework (version 4). The same is not true for Entity Framework version 3.5 since it does not support the DefaultIfEmpty keyword. To perform Left Outer Joins with Entity Framework 3.5, we need to create appropriate relationships (e.g 0..1 to 0..Many) in our Entity Model and they will be automatically translated into TSQL’s Left Join clause.

Saturday, October 3, 2015

How to make paging in collections

0 comments Posted by Duc Nguyen at 10:23 PM

Class for paging for generic collections

Import namespaces

using System.Collections;
using System.Collections.Generic;
using System.Linq;

Class for paging

public class PagingCollection : IEnumerable
{
    #region fields
    private const int DefaultPageSize = 10;
    private readonly IEnumerable _collection;
    private int _pageSize = DefaultPageSize;
    #endregion

    #region properties
    /// 
    /// Gets or sets page size
    ///
public int PageSize { get { return _pageSize; } set { if (value <= 0) { throw new ArgumentException(); } _pageSize = value; } } /// /// Gets pages count /// public int PagesCount { get { return (int)Math.Ceiling(_collection.Count() / (decimal)PageSize); } } #endregion #region ctor /// /// Creates paging collection and sets page size /// public PagingCollection(IEnumerable collection, int pageSize) { if (collection == null) { throw new ArgumentNullException("collection"); } PageSize = pageSize; _collection = collection.ToArray(); } /// /// Creates paging collection /// public PagingCollection(IEnumerable collection) : this(collection, DefaultPageSize) { } #endregion #region public methods /// /// Returns data by page number /// public IEnumerable GetData(int pageNumber) { if (pageNumber < 0 || pageNumber > PagesCount) { return new T[] { }; } int offset = (pageNumber - 1) * PageSize; return _collection.Skip(offset).Take(PageSize); } /// /// Returns number of items on page by number /// public int GetCount(int pageNumber) { return GetData(pageNumber).Count(); } #endregion #region static methods /// /// Returns data by page number and page size /// public static IEnumerable GetPaging(IEnumerable collection, int pageNumber, int pageSize) { return new PagingCollection(collection, pageSize).GetData(pageNumber); } /// /// Returns data by page number /// public static IEnumerable GetPaging(IEnumerable collection, int pageNumber) { return new PagingCollection(collection, DefaultPageSize).GetData(pageNumber); } #endregion #region IEnumerable Members /// /// Returns an enumerator that iterates through collection /// public IEnumerator GetEnumerator() { return _collection.GetEnumerator(); } #endregion #region IEnumerable Members /// /// Returns an enumerator that iterates through collection /// IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } #endregion }

Use

// fill test data
var data = new List<int>();
for (int i = 0; i < 25; i++)
{
    data.Add(i);
}

// create paging collection
var paging = new PagingCollection<int>(data);
// set page size
paging.PageSize = 6;

// get number of pages
Console.WriteLine("Pages count: {0}", paging.PagesCount);

// iterate through pages
for (int i = 1; i <= paging.PagesCount; i++)
{
    // get number of items on page
    Console.WriteLine("Page: {0} ({1} items)", i, paging.GetCount(i));

    // get data by page number
    foreach (int number in paging.GetData(i))
    {
        Console.WriteLine(number);
    }
}

Scrollable Repeater with Fixed Header in ASP.Net

0 comments Posted by Duc Nguyen at 10:15 PM

Making the Header Fixed and Rows Scrollable in Repeater
The Repeater’s Header will be fixed and rows will be made Scrollable using jQuery Scrollable Table plugin.
The plugin will be applied to the Table element.
 

Subscribe to: Posts (Atom)