Solved

c#, LINQ Group By

Posted on 2014-11-14
11
354 Views
Last Modified: 2016-02-15
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

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444424
How do you implement, "public partial class Search" in WSCGSoftware?
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444532
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444534
Can you post it here please?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444544
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444547
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444563
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444567
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
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40444571
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444575
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
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40444620
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444706
Not a problem Mike, glad to help.

Will look at new question.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question