c#, LINQ Group By

wscgsContext.Search returns recordset with fields like [First Name], [Last Name], and [City].

Question 1: wscgsContext.Search <-- shows an error:
WSCGSoftwareDataContext does not contain a definition for 'Search' and no extension method accepting a first argument of type WSCGSoftwareDataContext could be found (are you missing a using directive or an assembly reference?).

Question 2: How can I complete the LINQ on line 5 to group and sort it for [City] only?

Optionally we can use Distinct if it has a better performance.
        string cs = ConfigurationManager.ConnectionStrings["WSCGSoftwareConnectionString"].ConnectionString;
        SqlConnection cnn =new SqlConnection(cs);
        WSCGSoftwareDataContext wscgsContext =  new WSCGSoftwareDataContext(cnn);
FYI: I have  "public partial class Search" in WSCGSoftware.Designer.cs.
If assembly is missing, I cannot figure out what is missing. Could this possibly be a name space issue?

        dynamic outputs = from output in wscgsContext.Search select output.City;

        foreach (WSCGSoftwareDataContext search in outputs)
        {
            cmbCity.Items.Add(outputs.City);
        }

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
How do you implement, "public partial class Search" in WSCGSoftware?
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I have  "public partial class Search" in WSCGSoftware.Designer.cs. This is automatically created.
If assembly is missing, I cannot figure out what is missing. Could this possibly be a name space issue?

Search table is a temp table. At this point it doesn't have PK in it if this makes any difference.
0
Fernando SotoRetiredCommented:
Can you post it here please?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Here you are:dbml_filePlease note #pragma warning disable 1591at line 1 in WSCGSoftware.Designer.cs :
#pragma warning disable 1591
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.18444
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;



[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="WSCGSoftware")]
public partial class WSCGSoftwareDataContext : System.Data.Linq.DataContext
{
	
	private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
	
  #region Extensibility Method Definitions
  partial void OnCreated();
  #endregion
	
	public WSCGSoftwareDataContext() : 
			base(global::System.Configuration.ConfigurationManager.ConnectionStrings["WSCGSoftwareConnectionString"].ConnectionString, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(string connection) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(System.Data.IDbConnection connection) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public System.Data.Linq.Table<Search> Searches
	{
		get
		{
			return this.GetTable<Search>();
		}
	}
}

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Search")]
public partial class Search
{
	
	private string _First_Name;
	
	private string _Last_Name;
	
	private string _Address;
	
	private string _City;
	
	private string _State;
	
	private string _Zip_Code;
	
	private string _Phone;
	
	private string _Email;
	
	private string _Order_Date;
	
	private string _Title;
	
	private string _Unit_Price;
	
	private string _Quantity;
	
	public Search()
	{
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[First Name]", Storage="_First_Name", DbType="VarChar(30)")]
	public string First_Name
	{
		get
		{
			return this._First_Name;
		}
		set
		{
			if ((this._First_Name != value))
			{
				this._First_Name = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Last Name]", Storage="_Last_Name", DbType="VarChar(30)")]
	public string Last_Name
	{
		get
		{
			return this._Last_Name;
		}
		set
		{
			if ((this._Last_Name != value))
			{
				this._Last_Name = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Address", DbType="VarChar(100)")]
	public string Address
	{
		get
		{
			return this._Address;
		}
		set
		{
			if ((this._Address != value))
			{
				this._Address = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_City", DbType="VarChar(30)")]
	public string City
	{
		get
		{
			return this._City;
		}
		set
		{
			if ((this._City != value))
			{
				this._City = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_State", DbType="VarChar(30)")]
	public string State
	{
		get
		{
			return this._State;
		}
		set
		{
			if ((this._State != value))
			{
				this._State = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Zip Code]", Storage="_Zip_Code", DbType="VarChar(20)")]
	public string Zip_Code
	{
		get
		{
			return this._Zip_Code;
		}
		set
		{
			if ((this._Zip_Code != value))
			{
				this._Zip_Code = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Phone", DbType="VarChar(20)")]
	public string Phone
	{
		get
		{
			return this._Phone;
		}
		set
		{
			if ((this._Phone != value))
			{
				this._Phone = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Email", DbType="VarChar(100)")]
	public string Email
	{
		get
		{
			return this._Email;
		}
		set
		{
			if ((this._Email != value))
			{
				this._Email = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Order Date]", Storage="_Order_Date", DbType="VarChar(30)")]
	public string Order_Date
	{
		get
		{
			return this._Order_Date;
		}
		set
		{
			if ((this._Order_Date != value))
			{
				this._Order_Date = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Title", DbType="VarChar(30)")]
	public string Title
	{
		get
		{
			return this._Title;
		}
		set
		{
			if ((this._Title != value))
			{
				this._Title = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Unit Price]", Storage="_Unit_Price", DbType="VarChar(30)")]
	public string Unit_Price
	{
		get
		{
			return this._Unit_Price;
		}
		set
		{
			if ((this._Unit_Price != value))
			{
				this._Unit_Price = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Quantity", DbType="VarChar(10)")]
	public string Quantity
	{
		get
		{
			return this._Quantity;
		}
		set
		{
			if ((this._Quantity != value))
			{
				this._Quantity = value;
			}
		}
	}
}
#pragma warning restore 1591

Open in new window

WSCGSoftware.dbml.layout :
<?xml version="1.0" encoding="utf-8"?>
<ordesignerObjectsDiagram dslVersion="1.0.0.0" absoluteBounds="0, 0, 11, 8.5" name="WSCGSoftware">
  <DataContextMoniker Name="/WSCGSoftwareDataContext" />
  <nestedChildShapes>
    <classShape Id="a37c36b4-c163-48d7-9792-a3cbfa22cb55" absoluteBounds="0.5, 0.5, 2, 3.1170068359375">
      <DataClassMoniker Name="/WSCGSoftwareDataContext/Search" />
      <nestedChildShapes>
        <elementListCompartment Id="116a49d8-f500-4d51-98bc-ee1d88ce3bcf" absoluteBounds="0.51500000000000012, 0.96, 1.9700000000000002, 2.5570068359375" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
      </nestedChildShapes>
    </classShape>
  </nestedChildShapes>
</ordesignerObjectsDiagram>

Open in new window

0
Fernando SotoRetiredCommented:
Hi eghtebas;

Change this line in your code:

dynamic outputs = from output in wscgsContext.Search select output.City;

to this:

dynamic outputs = from output in wscgsContext.Searches select output.City;

The mapping between the code and the database table is Searches (In code)  To  Search (Database Table)
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much for the correction, now I have a similar confusion in the following lines:

       foreach (WSCGSoftwareDataContext output in outputs)
        {
            cmbCity.Items.Add(outputs.City);
        }

Cannot convert type 'string' to 'WSCGSoftwareDataContext'
0
Fernando SotoRetiredCommented:
In answer to question two try it like this.
dynamic outputs = (from output in wscgsContext.Searches
                   group output by new { Key = output.City } into cityGroup
                   select cityGroup.Key).Distinct().OrderBy(c => c );

Open in new window

0
Fernando SotoRetiredCommented:
Hi eghtebas;

That is because the query is returning a collection of type String, the City column values. Try it like this.

       foreach (String output in outputs)
        {
            cmbCity.Items.Add(outputs);
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
cmbCity.Items.Add(outputs); without s works for the old code.

But, with the new (group by code), at foreach I get: Cannot order by type '<>f__AnonymousType0`1[System.String]'.

Just to test without order by, when I used:
       dynamic outputs = (from output in wscgsContext.Searches
                         group output by new { Key = output.City } into cityGroup
                           select cityGroup.Key).Distinct();//.OrderBy(c => c);

        foreach (String output in outputs)
        {
            cmbCity.Items.Add(output);
        }

Open in new window

I got Cannot convert type '<>f__AnonymousType0<string>' to 'string' at foreach (String output in outputs)
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you Fernando for wonderful help. I am closing this question and open a new question for pending group by/distinct part. I will post a link here.

Regards,

Mike

Please see: http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_28562829.html
0
Fernando SotoRetiredCommented:
Not a problem Mike, glad to help.

Will look at new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.