How to Fetch Data from database using Entity Framework 6

Question

I have build a query to return data from two tables in which they are joined by inner join. Although, as the query seems fine, i am getting error message when i try to access the selected field names from the query. How do i use .SingleOrDefault() function in this query. Can anybody help me how should i proceed.

private void FindByPincode(int iPincode)
    {
        using (ABCEntities ctx = new ABCEntities())
        {
            var query = from c in ctx.Cities
                        join s in ctx.States
                        on c.StateId equals s.StateId
                        where c.Pincode == iPincode
                        select new {
                                s.StateName, 
                                c.CityName, 
                                c.Area};

            // var query = ctx.Cities.AsNoTracking().SingleOrDefault(_city => _city.Pincode == iPincode);

            if (query != null)
            {
                cboState.SelectedItem.Text =query.State;        //Getting error "Could not found"
                cboCity.SelectedItem.Text = query.CityName;     //Getting error "Could not found"
                txtArea.Text = query.Area;                        //Getting error "Could not found"

            }

        }
    }

Thanks in advance.

Error is "'System.Linq.IQueryable<AnonymousType#1>' does not contain a definition for 'StateName' and no extension method 'StateName' accepting a first argument of type 'System.Linq.IQueryable<AnonymousType#1>' could be found (are you missing a using directive or an assembly reference?)" — Jaan
– Jaan, Commented Nov 25, 2013 at 17:54

Gopi · Accepted Answer · 2020-03-17 04:15:01Z

7

Try this:

using (ABCEntities ctx = new ABCEntities())
    {
        var query = (from c in ctx.Cities
                    join s in ctx.States
                    on c.StateId equals s.StateId
                    where c.Pincode == iPincode
                    select new {
                            s.StateName, 
                            c.CityName, 
                            c.Area}).FirstOrDefault();

        if (query != null)
        {
            cboState.SelectedItem.Text =query.State;        
            cboCity.SelectedItem.Text = query.CityName;    
            txtArea.Text = query.Area;                        
        }
    }

edited Mar 17, 2020 at 4:15

Gopi

5,91723 gold badges91 silver badges152 bronze badges

answered Nov 25, 2013 at 17:49

Jurgen Vandw

6519 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Monojit Sarkar Over a year ago

i saw your code but do not understand why you use .First() and SingleOrDefault() both to fetch data.... .First().SingleOrDefault(); would you please tell me the reason ?

CaveCoder · Accepted Answer · 2013-11-25 17:48:47Z

0

can it be that you are selecting a field named StateName, and then acessing State.

 cboState.SelectedItem.Text =query.State;    



cboState.SelectedItem.Text =query.StateName;

Please provide more details on the error and the class structure of your code

answered Nov 25, 2013 at 17:48

CaveCoder

7913 silver badges17 bronze badges

Comments

Talha Imam · Accepted Answer · 2017-02-14 10:54:06Z

0

Here's how a similar situation worked for me:

using (ABCEntities ctx = new ABCEntities())
{
        var query = (from c in ctx.Cities
                    join s in ctx.States
                    on c.StateId equals s.StateId
                    where c.Pincode == iPincode
                    select new {
                            s.StateName, 
                            c.CityName, 
                            c.Area}).Single();

        if (query.Any())
        {
            cboState.SelectedItem.Text =query.State;
            cboCity.SelectedItem.Text = query.CityName;
            txtArea.Text = query.Area;
        }
}

Notice that i used query.Any() up there, that is because query != null will always return true. However any() checks if a query has returned 1 or more records, if yes Any() returns true and if a query returns no records then Any() returns false.

answered Feb 14, 2017 at 10:54

Talha Imam

1,1161 gold badge21 silver badges23 bronze badges

2 Comments

Sorangwala Abbasali Over a year ago

What if query returns default if not single(). Then it would turn an error/ exception.

Talha Imam Over a year ago

@SorangwalaAbbasali You can use SingleOrDefault() for such a situation.

Collectives™ on Stack Overflow

How to Fetch Data from database using Entity Framework 6

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related