[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

How do I use LINQ query results?

Hi,

I have the attached linq query which populates my frontend asp.net listview control....

After my query I have code declaring the array "rateValues". How can I fill this array with selected columns returned from my linq query? I need this array as it's used to populate a frontend rating control. Currently "query.Rating01..." returns the error: "System.Linq.IQueryable does not contain a definition for Rating01..."? I also get an error on my UserId reference (code line 73)?

Thanks.
var query =
                            (
                                from tt in db.tblTraders
                                join ttcl in db.tblCityLookups on tt.city_id equals ttcl.city_id
                                join lctt in db.tblCountryLookups on tt.country_id equals lctt.country_id
                                join tcl in db.tblCountyLookups on tt.county_id equals tcl.county_id
                                join twal in db.tblTraderWorkingAreaLookups on tt.trader_working_area_id equals twal.trader_working_area_id
                                join ttrade in db.tblTraderTrades on tt.UserId equals ttrade.UserId
                                join trade in db.tblTrades on ttrade.trade_id equals trade.trade_id
                                join ttt in db.tblTradeTradeTypes on trade.trade_id equals ttt.trade_id
                                join tradet in db.tblTradeTypes on ttrade.trade_type_id equals tradet.trade_type_id
                                join cj in db.tblCustomerJobs on trade.trade_id equals cj.trade_id
                                join tr in db.tblTraderRatings on tt.UserId equals tr.UserId
                                //join tc in db.tblCustomers on cj.UserId equals tc.UserId
                                orderby tt.trader_firstname ascending
                                select new
                                {
                                    tt.UserId
                                  ,
                                    tt.trader_title
                                  ,
                                    tt.trader_firstname
                                  ,
                                    tt.trader_surname
                                  ,
                                    tt.trader_phone1
                                  ,
                                    tt.trader_phone2
                                  ,
                                    tt.trader_working_area_id
                                  ,
                                    tt.trader_overall_rating
                                  ,
                                    trader_working_area_name = twal.trader_working_area_name.Substring(0, twal.trader_working_area_name.IndexOf('(')).Trim()
                                  ,
                                    trader_working_area_postcodes = twal.trader_working_area_name.Substring(twal.trader_working_area_name.IndexOf('(') + 1, twal.trader_working_area_name.LastIndexOf(')') - twal.trader_working_area_name.IndexOf('(') - 1).Trim()
                                  ,
                                    trade_type_name = tradet.trade_type_name == null ? "" : tradet.trade_type_name
                                  ,
                                    trade.trade_name
                                  ,
                                    ttrade.trade_type_id
                                  ,
                                    ttrade.trade_id
                                  ,
                                    tr.Rating01
                                  ,
                                    tr.Rating02
                                  ,
                                    tr.Rating03
                                  ,
                                    tr.Rating04
                                  ,
                                    tr.Rating05
                                }
                            ).Distinct();

            e.Result = query;

            int[] rateValues;

            rateValues = new int[] 
            { 
                query.Rating01
                , query.Rating02
                , query.Rating03
                , query.Rating04
                , query.Rating05 
            };

            Spaanjaars.Toolkit.ContentRating ratingControl = (Spaanjaars.Toolkit.ContentRating)lvTrustAccounts.FindControl("ContentRating1");
            
            ratingControl.ItemId = UserId;
            ratingControl.DataSource = rateValues;
            ratingControl.DataBind();

Open in new window

0
aspnet-scotland
Asked:
aspnet-scotland
  • 7
  • 5
1 Solution
 
eguilhermeCommented:
just a guess, but i think that the distinct method will not make the query.. to get the results u need to call like First, FirstOrDefault,ToList etc..

try to change at line 56 if you are 100% sure it will return a value
).Distinct().First();

if you are not sure try this:
).Distinct().FirstOrDefault();
and add a if like this
if(query != null)
{

}

0
 
tovvenkiCommented:
yes equilherme is correct you need to call first or some other method to get the results.
Also when using distinct to get distinct values from a custom collection class you have to implement the IEqualityComparer(T) generic interface in the class or use group or else the result may not come correctly.

If you have problem then you can refer to the following links for more details on the same
http://msdn.microsoft.com/en-us/library/bb348436.aspx
http://www.experts-exchange.com/Programming/Languages/.NET/LINQ/Q_24079889.html

Thanks and regards,
Venki
0
 
aspnet-scotlandAuthor Commented:
Venki,

Would you be able to alter my code to shown me what you mean?

Thanks.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tovvenkiCommented:
first question are you getting the distinct values or duplicates are there.
0
 
aspnet-scotlandAuthor Commented:
I'm actually now getting the error:"Object reference not set to an instance of an object" on line 73. I think this is due to the debugger not being able to find my "ratingControl" reference as I can see that query.UserId returns a value. Would you know how to find a control within the ItemTemplate of an asp.net listview control through it's linqdatasource selecting event?
0
 
tovvenkiCommented:
try using FindControl it should help in finding controls
what does lvTrustAccounts.FindControl("ContentRating1") returns
0
 
aspnet-scotlandAuthor Commented:
it returns null, that's my problem.
0
 
tovvenkiCommented:
I assume that you want to a reference to the control in the listview selected item so try using findcontrol on the selecteditem

(Spaanjaars.Toolkit.ContentRating)lvTrustAccounts..Items(lvTrustAccounts.SelectedIndex).FindControl("ContentRating1")

I do not have VS right now to test this. I hope that this helps you.

Thanks and regards,
Venki
0
 
aspnet-scotlandAuthor Commented:
I used the attached code but I seem to get the error: "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"? When debugging my SelectedIndex seems to be -1?

Thanks.
Spaanjaars.Toolkit.ContentRating ratingControl = (Spaanjaars.Toolkit.ContentRating)lvTrustAccounts.Items[lvTrustAccounts.SelectedIndex].FindControl("ContentRating1");

Open in new window

0
 
tovvenkiCommented:
where are you are trying to run this code I mean in which event handler/method.
Was any item selected in the listview??
0
 
aspnet-scotlandAuthor Commented:
tovvenki,

I'm running this could from my listview's linqdatasource OnSelecting event and the listview should return one item....
protected void TrustsDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        TradeSelectorDataContext db = new TradeSelectorDataContext();

        var query =
                            (
                                from tt in db.tblTraders
                                join ttcl in db.tblCityLookups on tt.city_id equals ttcl.city_id
                                join lctt in db.tblCountryLookups on tt.country_id equals lctt.country_id
                                join tcl in db.tblCountyLookups on tt.county_id equals tcl.county_id
                                join twal in db.tblTraderWorkingAreaLookups on tt.trader_working_area_id equals twal.trader_working_area_id
                                join ttrade in db.tblTraderTrades on tt.UserId equals ttrade.UserId
                                join trade in db.tblTrades on ttrade.trade_id equals trade.trade_id
                                join ttt in db.tblTradeTradeTypes on trade.trade_id equals ttt.trade_id
                                join tradet in db.tblTradeTypes on ttrade.trade_type_id equals tradet.trade_type_id
                                join cj in db.tblCustomerJobs on trade.trade_id equals cj.trade_id
                                join tr in db.tblTraderRatings on tt.UserId equals tr.UserId
                                //join tc in db.tblCustomers on cj.UserId equals tc.UserId
                                orderby tt.trader_firstname ascending
                                select new
                                {
                                    tt.UserId
                                  ,
                                    tt.trader_title
                                  ,
                                    tt.trader_firstname
                                  ,
                                    tt.trader_surname
                                  ,
                                    tt.trader_phone1
                                  ,
                                    tt.trader_phone2
                                  ,
                                    tt.trader_working_area_id
                                  ,
                                    tt.trader_overall_rating
                                  ,
                                    trader_working_area_name = twal.trader_working_area_name.Substring(0, twal.trader_working_area_name.IndexOf('(')).Trim()
                                  ,
                                    trader_working_area_postcodes = twal.trader_working_area_name.Substring(twal.trader_working_area_name.IndexOf('(') + 1, twal.trader_working_area_name.LastIndexOf(')') - twal.trader_working_area_name.IndexOf('(') - 1).Trim()
                                  ,
                                    trade_type_name = tradet.trade_type_name == null ? "" : tradet.trade_type_name
                                  ,
                                    trade.trade_name
                                  ,
                                    ttrade.trade_type_id
                                  ,
                                    ttrade.trade_id
                                  ,
                                    tr.Rating01
                                  ,
                                    tr.Rating02
                                  ,
                                    tr.Rating03
                                  ,
                                    tr.Rating04
                                  ,
                                    tr.Rating05
                                }
                            ).Distinct().First();

            e.Result = query;

            int[] rateValues;

            rateValues = new int[]  
            {  
                query.Rating01 
                , query.Rating02 
                , query.Rating03 
                , query.Rating04 
                , query.Rating05  
            };

            //Spaanjaars.Toolkit.ContentRating ratingControl = (Spaanjaars.Toolkit.ContentRating)lvTrustAccounts.FindControl("ContentRating1");
            Spaanjaars.Toolkit.ContentRating ratingControl = (Spaanjaars.Toolkit.ContentRating)lvTrustAccounts.Items[lvTrustAccounts.SelectedIndex].FindControl("ContentRating1");

            ratingControl.ItemId = query.UserId;
            ratingControl.DataSource = rateValues;
            ratingControl.DataBind();
        }

Open in new window

0
 
aspnet-scotlandAuthor Commented:
and my listview and linq datasource look like...
<asp:LinqDataSource 
                                            ID="TrustsDataSource" 
                                            runat="server" 
                                            ContextTypeName="TradeSelectorDataContext"
                                            TableName="tblCustomers" 
                                            OnSelecting="TrustsDataSource_Selecting"/>
                                        <asp:ListView 
                                            ID="lvTrustAccounts" 
                                            DataSourceID="TrustsDataSource"
                                            runat="server"
                                            DataKeyNames="UserId"
                                            DataMember="DefaultView">

Open in new window

0
 
aspnet-scotlandAuthor Commented:
The find control code required is below:

Spaanjaars.Toolkit.ContentRating ratingControl = (Spaanjaars.Toolkit.ContentRating)sender;

Leading to the below required method within the Rating_DataBnding event...

using (TradeSelectorDataContext db = new TradeSelectorDataContext())
        {
            int[] rateValues;


            Spaanjaars.Toolkit.ContentRating ratingControl = (Spaanjaars.Toolkit.ContentRating)sender;


            var usrId = ((Guid)ratingControl).ItemId;


            // Get the item through LINQ. SingleOrDefault returns NULL when the item is not found.
            tblTraderRating result = (from tr in db.tblTraderRatings
                                      where tr.UserId == usrId)
                                      select tr).SingleOrDefault();


            if (result == null)
            {
                // Initialize to zero's
                rateValues = new int[] { 0, 0, 0, 0, 0 };
            }
            else
            {
                // Get the values from the result Rating instance.
                rateValues = new int[]
                {
                    result.Rating01
                    , result.Rating02
                    , result.Rating03
                    , result.Rating04
                    , result.Rating05
                };
            }

            //ratingControl.ItemId = UserId;
            ratingControl.DataSource = rateValues;
        }
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now