Linq to SQL where clause against collection of list values

Problem:

I was working on an EF Model with Linq 2 SQL.  

I sometimes depend on the SQL sp's which depend upon subqueries

select x.value1,x.value2 from myTable x

where

x.primarykey in (select foreignkey from myTable2 where name="jump")

How do you accomplish this in Linq?

Solution:

var innerquery = (from iq in myTable2

where iq.name.Equals("jump")

select iq.id);

var Ids = innerquery.ToList();

var query = 

from mt in myTable

where

Ids.Any(qq => mt.Id.Equals(qq)

select mt);

Source:

http://stackoverflow.com/questions/1075540/linq-to-sql-how-to-do-where-column-in-list-of-values

.NET: Combining two generic lists

 

Problem:

I had created two List collections based on a some predicate searches against an EF model.
I need to blend the 2 lists together

Solution:

The AddRange method did the trick

List<Type> list1;
List<Type> list2;

List<Type> combined;
combined.AddRange(list1);
combined.AddRange(list2);

Source:

http://stackoverflow.com/questions/2002770/net-combining-two-generic-lists

Convert nullable bool? to bool

I was trying to set up a complex query screen in MVC.  I have some checkboxes which may or may not be used in the query.  I needed to cast the nullable bool to bool

bool newBool = x.HasValue ? x.Value : false;

Source:
http://stackoverflow.com/questions/6075726/convert-nullable-bool-to-bool

How To Create Sub Dropdown menu in MVC 5 ?

Problem:

I needed a way to put a submenu into a menu in bootstrap in MVC 5 project.  The folks at bootstrap don't support this anymore.

Solution:

CSS:

#nav ul{
    display : none;
}
#nav li:hover > ul{
    display : block;
}

    <ul id="nav">
      <li><a href="#">Menu1</a></li>
      <li><a href="#">Menu2</a></li>
      <li><a href="#">Menu3</a></li>
      <li>
        <a href="#">Menu4</a>
        <ul>
            <li><a href="#">SubMenu1</a></li>
            <li><a href="#">SubMenu2</a></li>
            <li><a href="#">SubMenu3</a>
                <ul>
                    <li><a href="#">SubMenu11</a></li>
                    <li><a href="#">SubMenu22</a></li>
                </ul>
            </li>
        </ul>
      </li>
    </ul>

Source:

https://forums.asp.net/t/1987573.aspx?How+To+Create+Sub+Dropdown+menu+in+MVC+5+

Using DisplayFor on model's nested properties

Problem:
My MVC model has a property which is a Generic list of another class.  I needed to iterate across this collection using Razor Syntax.

Solution:
I found a great solution at stackoverflow

You have to iterate your list and call display for on each item:

@foreach (var summary in Model.CollectionClass)
{
    @Html.DisplayFor(x => summary.Balance)
}

Source:
http://stackoverflow.com/questions/25061233/using-displayfor-on-models-nested-properties

accepted

CompTIA Security+ ce renewal

I just completed renewing the Security+ certification issued by COMPTIA.  This is requirement
for all contracting staff who work with DOD.  This has been a requirement for some years now.

Security+ CE program runs on a 3 year cycle.  That is from the time you successfully pass the Security+ exam you have a 3 year period where the following must occur in order to maintain
your certification:

1.) Pay Comptia $50 a year.  You can pay this annually or all at once your choice.
2.) Acquire 50 Continuing Education Units over the period of 3 years.
3.) Add CEUs into the comptia website as you acquire the CEU.  Note:  if you have not paid the annual fee then you will be unable to complete this step.

How to acquire the 50 CEUs?  While this can be reached in a number of ways, see items 1 & 2 under sources, I will describe the path I chose:

1. Complete the Cyber Security Fundamentals Training (item 3) from Ft. Gordon this will give you 40 CEUs.
2. Write your job description and have your boss sign and date the description on company letter head.  You get 3 CEUs for each year of work which requires you to use Security+ skillsets. That means for the 3 year timespan you get 9 CEUs.
3. Complete 1 course from the DISA courseware list (item 1).  I chose the DNS Basic concepts (item 4).

That gave me my 50 CEUs!

Source:

1. List of DISA courses available
2. List of DOD courses
3. Cyber Security Fundamentals Training (40 CEUs)
4. Domain Name System (DNS) Basic Concepts Overview Ver. 1.0 (1 CEU)
5. CompTIA Certification Site
6. Army Training & Certification Tracking System

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery' to type of 'System.Web.Mvc.SelectList'

Problem:

Had a solution using the Entity Framework which required a query modification. 


Solution was:
return new SelectList(db.Offices, "Office_ID", "Office_Name");


Changed too:


public class SecurityGroup
{
public string ADGroupSID { get; set; }
public string ADGroupName { get; set; }
public Guid Office_ID { get; set; }
public string ADUser { get; set; }
}


List<FCJC.Model.SecurityGroup> userOffice = theUser.GroupMembership.FindAll(g => ControllerHelper.GetAllOffices().Contains(g.Office_ID)).ToList();









var test = (from o in db.Offices

             join oa in db.OfficeADGroups on o.Office_ID equals oa.Office_ID
           where oa.ADGroup_SID.Equals(userOffice.Select(uo => uo.ADGroupSID).ToString())
select new
{
o.Office_ID,
o.Office_Name
}).AsEnumerable();
return new SelectList(test, "Office_ID", "Office_Name");


The problem did not arise until it ran.  The query compiled but since we are dealing with LINQ the query did not execute until runtime.  Then I received the following error:

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType9`2[System.Guid,System.String]]' to type 'System.Web.Mvc.SelectList'

Solution:

The problem was the lack of support from Entity Framework.  It will only support primitive data types.  The query uses anonymous typing until it is executed.  This process then tries to resolve the data type.  This will not support the use of custom data types.  I tried a number of work arounds to include forcing the execution of the query and then applying a cast to the SelectListItem type.


Try 1 - Fail
 var test = (from o in db.Offices
  ().Select(o => o.Office_ID) on oa.Office_ID equals uo.Office_ID
                        select new
                        {
                            o.Office_ID,
                            o.Office_Name
                        }).AsEnumerable()
            .Where(o => o.Office_ID.Equals(userOffice.Select(uo => uo.Office_ID)))
         
            new SelectListItem
            {
                Value = x.Office_ID.ToString(),
                Text = x.Office_Name
            });


This will work in Linq to SQL but is not supported in the current version of Entity Framework (v6).
The inclusion of the userOffice LINQ subquery is not supported in EF which will only support constant values.  That ruled out any sort of variable or collection.


Got it finally!


Solution was to take the LINQ out and do a direct query:


   using (var ctx = new FCJCModel())
            {
                var sql = "select Office_ID,Office_Name,Address_ID,Archive,LastUpdatedDate,LastUpdatedUser from office where Office_ID in (" + ofcGuids.Replace("\"", "'") + ")";
                var kk = ctx.Offices.SqlQuery(sql).ToList();
                return new SelectList(kk, "Office_ID", "Office_Name");
            }

Source:

1. http://stackoverflow.com/questions/15211362/only-primitive-types-or-enumeration-types-are-supported-in-this-context
2. http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx