asked on

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

Fernando Soto

How do you implement, "public partial class Search" in WSCGSoftware?

Mike Eghtebas

ASKER

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.

Fernando Soto

Can you post it here please?

Mike Eghtebas

ASKER

Here you are:

Please 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

Fernando Soto

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)

Mike Eghtebas

ASKER

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'

Fernando Soto

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

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

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)

Mike Eghtebas

ASKER

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: https://www.experts-exchange.com/questions/28562829/Cannot-order-by-type-'-f-AnonymousType0-1-System-String-'-c-LINQ.html

Fernando Soto

Not a problem Mike, glad to help.

Will look at new question.