I have a master and child table. If I hand write the linq statement it is:
var rs = from c in db.QRCampaigns where c.UserId == iUserId orderby c.QRCampaignId descending
select new { c.QRCampaignId, c.Name, c.URL, hits = c.QRHitLogs.Count() };
but I want to take advantage of the GridView Paging and Sorting; therefore, I've created a asp:LinkDataSource in the .aspx. For the life of me, I cannot figure out how to add the child.Count() to the selection on the DataSource.
from c in _db.CampaignHits
where c.UserId == iUserId
orderby c.Id descending
group c by new { c.QRCampaignId,c.URL,c.Name} into g
select new { g.Key.QRCampaignId,g.Key.URL,g.Key.Name, hits = g.Count(c=>c.QRHitLogs) };
Regards,
Chirag Vidani | My Blog MCTS - .Net Framework 4.0, Web Applications
I don't need to group the result. This list shows campaigns for a given user. Also in the asp:GridView, we want to show how many hits a campaign has had. I have this working with the original Linq statement and a asp:DataGrid; however, I would like to
take advantage of the automated Paging and Sorting on the GridView. Unfortunately, to do the auto Paging and Sorting you have to use an asp:LinqDataSource, which I have only found modifications to this through the graphical designer in Visual Studio, thus
no way to tie the Linq statement I know works to the GridView.
So, is there a way to build a LinqDataSource in the code behind? Is there a way to add a Count() of the sub table (hits) in the LinqDataSource?
Suppose your LINQ query is quite right. In fact You don't need to use any object datasources but——
Manually Databinding:
Just write a private function:
private void MyDatabind()
{
var rs = from c in db.QRCampaigns where c.UserId == iUserId orderby c.QRCampaignId descending select new { c.QRCampaignId, c.Name, c.URL, hits = c.QRHitLogs.Count() };
GridView1.DataSource = rs;
GridView1.DataBind();
}
And then call the method in the page_Load—— if(!IsPostBack)｛……｝ and PageIndexChanged event. Don't forget to enable the Paging for GridView!
svanniman
Member
14 Points
9 Posts
1:M Tables LinqDataSource M.Count()
Sep 08, 2011 11:35 PM|LINK
I have a master and child table. If I hand write the linq statement it is:
var rs = from c in db.QRCampaigns where c.UserId == iUserId orderby c.QRCampaignId descending
select new { c.QRCampaignId, c.Name, c.URL, hits = c.QRHitLogs.Count() };
but I want to take advantage of the GridView Paging and Sorting; therefore, I've created a asp:LinkDataSource in the .aspx. For the life of me, I cannot figure out how to add the child.Count() to the selection on the DataSource.
chiragvidani
Participant
1288 Points
254 Posts
Re: 1:M Tables LinqDataSource M.Count()
Sep 09, 2011 06:31 AM|LINK
Hello,
So you need to do group by count in LINQ.
Below is the example for it.
from c in _db.CampaignHits where c.UserId == iUserId orderby c.Id descending group c by new { c.QRCampaignId,c.URL,c.Name} into g select new { g.Key.QRCampaignId,g.Key.URL,g.Key.Name, hits = g.Count(c=>c.QRHitLogs) };Chirag Vidani | My Blog
MCTS - .Net Framework 4.0, Web Applications
"…Mark As Answer" if my reply is helpful to you…”
svanniman
Member
14 Points
9 Posts
Re: 1:M Tables LinqDataSource M.Count()
Sep 09, 2011 12:11 PM|LINK
Chirag,
I don't need to group the result. This list shows campaigns for a given user. Also in the asp:GridView, we want to show how many hits a campaign has had. I have this working with the original Linq statement and a asp:DataGrid; however, I would like to take advantage of the automated Paging and Sorting on the GridView. Unfortunately, to do the auto Paging and Sorting you have to use an asp:LinqDataSource, which I have only found modifications to this through the graphical designer in Visual Studio, thus no way to tie the Linq statement I know works to the GridView.
So, is there a way to build a LinqDataSource in the code behind? Is there a way to add a Count() of the sub table (hits) in the LinqDataSource?
Regards,
Steve
chohmann
Star
9385 Points
1644 Posts
Re: 1:M Tables LinqDataSource M.Count()
Sep 10, 2011 12:45 AM|LINK
<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="MyDataContext" TableName="QRCampaigns" Select="new (QRCampaignId, Name, URL, QRHitLogs.Count() AS hits)"> </asp:LinqDataSource>Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: 1:M Tables LinqDataSource M.Count()
Sep 11, 2011 12:41 AM|LINK
Hello svanniman,
Suppose your LINQ query is quite right. In fact You don't need to use any object datasources but——
Manually Databinding:
Just write a private function:
private void MyDatabind()
{
var rs = from c in db.QRCampaigns where c.UserId == iUserId orderby c.QRCampaignId descending
select new { c.QRCampaignId, c.Name, c.URL, hits = c.QRHitLogs.Count() };
GridView1.DataSource = rs;
GridView1.DataBind();
}
And then call the method in the page_Load—— if(!IsPostBack)｛……｝ and PageIndexChanged event. Don't forget to enable the Paging for GridView!
In your PageIndexChanging:
GridView1.PageIndex = e.NewPageIndex;